<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"  "http://www.w3.org/TR/html4/loose.dtd">

<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html"; charset="iso-8859-1">
		<meta name="keywords" content="CS2REP">
		<meta name="author" content="CHARONWARE - www.casestudio.com">

		<link rel="stylesheet" type="text/css" media="screen" href="aqua_blue.css" title="report">
		<link rel="stylesheet" type="text/css" media="print" href="aqua_blue_print.css" title="report">
		<title>CASE Studio 2 - HTML Report</title>
	</head>

<body class="rightPage">
<a name = "Procedure_7"></a>
<div class="caption1">GET_COST_FROM_STORE</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;GET_COST_FROM_STORE&quot; (&quot;SOURCESTORE&quot; Integer,&quot;TARGETSTORE&quot; Integer) <br> AS<br> <br> <br> <br> <br> DECLARE VARIABLE AVG_COST NUMERIC(15,4);<br> <br> DECLARE VARIABLE LAST_COST NUMERIC(15,4);<br> <br> DECLARE VARIABLE BRAND VARCHAR (30);<br> <br> DECLARE VARIABLE STYLE VARCHAR (30);<br> <br> DECLARE VARIABLE EXT   VARCHAR (8);<br> <br> DECLARE VARIABLE SIZ   VARCHAR (8);<br> <br> <br>  BEGIN<br> <br>      FOR SELECT distinct BRAND, STYLE, EXT, SIZ, AVG_COST, LAST_COST FROM INVT WHERE STORE = :sourceSTORE INTO :BRAND, :STYLE, :EXT, :SIZ, :AVG_COST, :LAST_COST<br> <br>      DO<br> <br>      BEGIN<br> <br>         UPDATE INVT SET AVG_COST = :AVG_COST, LAST_COST = :LAST_COST WHERE STORE = :targetSTORE AND BRAND = :BRAND AND STYLE =:STYLE AND EXT = :EXT AND SIZ = :SIZ;<br> <br>      END<br> <br>  END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_17"></a>
<div class="caption1">GET_INVT_DETAIL</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;GET_INVT_DETAIL&quot; (&quot;STORE_NUM&quot; Integer,&quot;DTE1&quot; Date,&quot;DTE2&quot; Date,&quot;BRAND1&quot; VarChar(30),&quot;STYLE1&quot; VarChar(30),&quot;EXT1&quot; VarChar(8)) RETURNS (&quot;DESCRIPTION&quot; VarChar(30),&quot;OH&quot; Integer,&quot;CUM_RCVD&quot; Integer,&quot;CUM_SOLD&quot; Integer,&quot;RCVD_PERIOD&quot; Integer,&quot;RCVD_PERIOD_COST&quot; Double precision,&quot;TRAN_IN_PERIOD&quot; Integer,&quot;TRAN_IN_PERIOD_COST&quot; Double precision,&quot;BO_PERIOD&quot; Integer,&quot;BO_PERIOD_COST&quot; Double precision,&quot;TRAN_OUT_PERIOD&quot; Integer,&quot;TRAN_OUT_PERIOD_COST&quot; Double precision,&quot;UNITS_SOLD_PERIOD&quot; Integer,&quot;REFUNDS_PERIOD&quot; Integer,&quot;INTRANSIT&quot; Integer,&quot;COST_INTRANSIT&quot; Double precision,&quot;TOTAL_UNITS_SOLD_PERIOD&quot; Integer,&quot;COST_SOLD_PERIOD&quot; Double precision,&quot;SALES_PERIOD&quot; Double precision,&quot;TOTAL_UNITS_SOLD_OUT_OF_PERIOD&quot; Integer,&quot;SALES_OUT_OF_PERIOD&quot; Double precision,&quot;COST_SOLD_OUT_OF_PERIOD&quot; Double precision,&quot;AVG_COST&quot; Double precision,&quot;PRICE&quot; Double precision,&quot;INVENTARIO_INICIAL&quot; Double precision,&quot;INVENTARIO_FINAL&quot; Double precision,&quot;RCVD_BEFORE&quot; Integer,&quot;RCVD_BEFORE_COST&quot; Double precision,&quot;UNITS_SOLD_BEFORE&quot; Integer,&quot;RCVD_AFTER&quot; Integer,&quot;RCVD_AFTER_COST&quot; Double precision,&quot;UNITS_SOLD_AFTER&quot; Integer,&quot;INVENTORY_COST&quot; Double precision,&quot;INVENTORY_PRICE&quot; Double precision,&quot;UNITS_SOLD_HISTORY&quot; Integer,&quot;SALES_HISTORY&quot; Double precision,&quot;CUM_COST_SOLD&quot; Double precision,&quot;INTRANSIT_PERIOD&quot; Integer,&quot;COST_INTRANSIT_PERIOD&quot; Double precision)<br> AS<br> <br> DECLARE VARIABLE units_sold_period_typ_0 INTEGER;<br> DECLARE VARIABLE units_sold_period_typ_1 INTEGER;<br> DECLARE VARIABLE TOTAL_UNITS_SOLD_PERIOD_TYP_0 INTEGER;<br> DECLARE VARIABLE SALES_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE COST_SOLD_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_UNITS_SOLD_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE SALES_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE COST_SOLD_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_BEFORE_TYP_0 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_BEFORE_TYP_1 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_AFTER_TYP_0 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_AFTER_TYP_1 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_OUT_OF_PERIOD_TYP_0 INTEGER;<br> DECLARE VARIABLE TOTAL_SOLD_OUT_OF_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE UNITS_SOLD_OUT_OF_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE TOTAL_SOLD_OUT_OF_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE PICKUP_DEDUCTIVE VARCHAR(5);<br> <br> <br> <br>  BEGIN<br> <br> <br>   SELECT OPTIONVALUE FROM STOREOPTIONS WHERE ID=144 AND STORE=:STORE_NUM INTO :PICKUP_DEDUCTIVE;<br> <br> <br>   /* INVENTARIO*/<br>     SELECT SUM(QUAN),MAX(AVG_COST),DESCRIPTION,SUM(INTRANSIT),MAX(PRICE)  FROM INVT WHERE STYLE= :style1 AND BRAND= :brand1<br>     and store= :store_num  AND EXT=:ext1 GROUP BY DESCRIPTION<br>     INTO :oh,:avg_cost,:description,:intransit,:price;<br> <br>     IF (:avg_cost is NULL OR :intransit is NULL) THEN<br>      BEGIN<br>       COST_INTRANSIT=0;<br>      END<br>     ELSE<br>         BEGIN<br>         COST_INTRANSIT=:intransit*:avg_cost;<br>         END<br> <br> <br>   SELECT sum(quan-quan_rcvd),sum(cost*(quan-quan_rcvd)) from TOLINE where closed=0 AND (quan-quan_rcvd)&lt;&gt;0<br>   AND DTE BETWEEN :DTE1 AND :DTE2 AND store=:STORE_NUM AND STYLE= :style1 AND BRAND= :brand1<br>   AND STORE= :store_num  AND EXT=:ext1 INTO :INTRANSIT_PERIOD,:COST_INTRANSIT_PERIOD;<br> <br>   IF (:INTRANSIT_PERIOD is NULL) THEN<br>      BEGIN<br>       INTRANSIT_PERIOD=0;<br>      END<br> <br>   IF (:COST_INTRANSIT_PERIOD is NULL) THEN<br>      BEGIN<br>       COST_INTRANSIT_PERIOD=0;<br>      END<br> <br> <br>   IF (:PICKUP_DEDUCTIVE='True' ) THEN<br>   BEGIN<br> <br>              /* UNIDADES VENDIDAS PERIODO*/<br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND<br>               RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>               AND QUAN&gt;0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP=0 INTO :units_sold_period_typ_0;<br> <br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND<br>               RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>               AND QUAN&gt;0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP&lt;&gt;0 AND RECEIPT.CLOSED=1 INTO :units_sold_period_typ_1;<br> <br>               IF (:units_sold_period_typ_0 IS NULL) THEN<br>                  units_sold_period_typ_0=0;<br> <br>               IF (:units_sold_period_typ_1 IS NULL) THEN<br>                  units_sold_period_typ_1=0;<br>               units_sold_period=:units_sold_period_typ_0+:units_sold_period_typ_1;<br> <br>           /* DEVOLUCIONES DE CLIENTE */<br>              SELECT SUM(QUAN) FROM RECLINE WHERE DTE BETWEEN :dte1 AND :dte2<br>              AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>              AND EXT=:ext1 INTO :refunds_period ;<br>              IF (:refunds_period IS NULL) THEN<br>                  refunds_period=0;<br> <br>          /* TOTAL VENTAS PERIODO*/<br>             SELECT SUM(QUAN),SUM(EXTENDED),SUM(QUAN*COST)<br>             FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>             AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>             AND EXT=:ext1 AND RECEIPT.TYP=0<br>             INTO :TOTAL_UNITS_SOLD_PERIOD_TYP_0,:sales_period_typ_0,:cost_sold_period_typ_0 ;<br> <br>            /* TOTAL VENTAS PERIODO*/<br>            SELECT SUM(QUAN),SUM(EXTENDED),SUM(QUAN*COST)<br>            FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>            AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>            AND EXT=:ext1 AND RECEIPT.TYP&lt;&gt;0 AND RECEIPT.CLOSED=1<br>            INTO :TOTAL_UNITS_SOLD_PERIOD_TYP_1,:sales_period_typ_1,:cost_sold_period_typ_1 ;<br> <br>            IF (:TOTAL_UNITS_SOLD_PERIOD_TYP_0 IS NULL) THEN<br>               TOTAL_UNITS_SOLD_PERIOD_TYP_0=0;<br>            IF (:TOTAL_UNITS_SOLD_PERIOD_TYP_1 IS NULL) THEN<br>               TOTAL_UNITS_SOLD_PERIOD_TYP_1=0;<br> <br>            IF (:sales_period_typ_0 IS NULL) THEN<br>                sales_period_typ_0=0;<br>            IF (:sales_period_typ_1 IS NULL) THEN<br>                sales_period_typ_1=0;<br> <br> <br>            IF (:COST_SOLD_PERIOD_TYP_0 IS NULL) THEN<br>               COST_SOLD_PERIOD_TYP_0=0;<br>            IF (:COST_SOLD_PERIOD_TYP_1 IS NULL) THEN<br>               COST_SOLD_PERIOD_TYP_1=0;<br> <br> <br> <br> <br>             TOTAL_UNITS_SOLD_PERIOD=:TOTAL_UNITS_SOLD_PERIOD_TYP_0+:TOTAL_UNITS_SOLD_PERIOD_TYP_1;<br>             SALES_PERIOD=:sales_period_typ_0 + :sales_period_typ_1;<br>             cost_sold_period=:cost_sold_period_typ_0+ :cost_sold_period_typ_1;<br> <br> <br> <br> <br> <br>  /*VENTAS ANTERIORES AL PERIODO */<br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>               AND RECEIPT.STORE=RECLINE.STORE AND  DTE &lt; :dte1<br>               AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP=0      INTO :units_sold_before_typ_0 ;<br> <br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>               AND RECEIPT.STORE=RECLINE.STORE AND DTE &lt; :dte1<br>               AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP&lt;&gt;0 and receipt.closed=1<br>               INTO :units_sold_before_typ_1 ;<br>               IF (:units_sold_before_typ_0 IS NULL) THEN<br>                  units_sold_before_typ_0=0;<br> <br>               IF (:units_sold_before_typ_1 IS NULL) THEN<br>                   units_sold_before_typ_1=0;<br> <br>               units_sold_before=:units_sold_before_typ_0+:units_sold_before_typ_1;<br> <br>                /*VENTAS POSTERIORES AL PERIODO */<br> <br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>                AND RECEIPT.STORE=RECLINE.STORE AND DTE &gt; :dte2<br>                AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>                AND EXT=:ext1 AND TYP=0 INTO :units_sold_after_typ_0 ;<br> <br>                SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>                AND RECEIPT.STORE=RECLINE.STORE AND DTE &gt; :dte2<br>                AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>                AND EXT=:ext1 AND TYP&lt;&gt;0 and receipt.closed=1<br>                INTO :units_sold_after_typ_1 ;<br>                 IF (:units_sold_after_typ_0 IS NULL) THEN<br>                     units_sold_after_typ_0=0;<br>                 IF (:units_sold_after_typ_1 IS NULL) THEN<br>                    units_sold_after_typ_1=0;<br> <br>                 units_sold_after=:units_sold_after_typ_0 + :units_sold_after_typ_1 ;<br> <br>   /*VENTAS HECHAS FUERA DEL PERIODO */<br>      SELECT SUM(QUAN),SUM(EXTENDED),SUM(QUAN*COST)<br>      FROM RECLINE,RECEIPT  WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND<br>      TYP=0 AND DTE NOT BETWEEN :dte1 AND :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :UNITS_SOLD_OUT_OF_PERIOD_TYP_0,:TOTAL_SOLD_OUT_OF_PERIOD_TYP_0,:TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0;<br> <br>      SELECT SUM(QUAN),SUM(EXTENDED),SUM(QUAN*COST)<br>      FROM RECLINE,RECEIPT  WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND<br>      TYP&lt;&gt;0 AND RECEIPT.CLOSED=1 AND DTE NOT BETWEEN :dte1 AND :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :UNITS_SOLD_OUT_OF_PERIOD_TYP_1,:TOTAL_SOLD_OUT_OF_PERIOD_TYP_1,:TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1;<br> <br> <br>      IF (UNITS_SOLD_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      UNITS_SOLD_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (UNITS_SOLD_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      UNITS_SOLD_OUT_OF_PERIOD_TYP_1=0;<br> <br>      IF (TOTAL_SOLD_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      TOTAL_SOLD_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (TOTAL_SOLD_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      TOTAL_SOLD_OUT_OF_PERIOD_TYP_1=0;<br> <br> <br> <br>      IF (TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1=0;<br> <br> <br> <br>      TOTAL_UNITS_SOLD_OUT_OF_PERIOD=:UNITS_SOLD_OUT_OF_PERIOD_TYP_0 + :UNITS_SOLD_OUT_OF_PERIOD_TYP_1;<br>      SALES_OUT_OF_PERIOD=:TOTAL_SOLD_OUT_OF_PERIOD_TYP_0 +:TOTAL_SOLD_OUT_OF_PERIOD_TYP_1;<br>      COST_SOLD_OUT_OF_PERIOD=:TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0+ :TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1;<br> <br> <br> <br>   END<br> <br>   ELSE<br> <br>   BEGIN<br> <br>    /* UNIDADES VENDIDAS PERIODO*/<br>    SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND<br>    RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>    AND QUAN&gt;0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>    AND EXT=:ext1<br>      INTO :units_sold_period;<br> <br> <br> <br> <br> <br>   IF (:units_sold_period IS NULL) THEN<br> <br>          units_sold_period=0;<br> <br> <br> <br> <br> <br>   /* DEVOLUCIONES DE CLIENTE */<br>      SELECT SUM(QUAN) FROM RECLINE WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :refunds_period ;<br>      IF (:refunds_period IS NULL) THEN<br> <br>          refunds_period=0;<br> <br> <br> <br> <br>  /* TOTAL VENTAS PERIODO*/<br>    SELECT SUM(QUAN),SUM(EXTENDED),SUM(QUAN*COST)<br>    FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>    AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>    AND EXT=:ext1<br>      INTO :TOTAL_UNITS_SOLD_PERIOD,:sales_period,:cost_sold_period ;<br> <br> <br>   IF (:TOTAL_UNITS_SOLD_PERIOD IS NULL) THEN<br>   TOTAL_UNITS_SOLD_PERIOD=0;<br> <br>   IF (:sales_period IS NULL) THEN<br>   sales_period=0;<br> <br> <br> <br>   IF (:COST_SOLD_PERIOD IS NULL) THEN<br>   COST_SOLD_PERIOD=0;<br> <br> <br> <br> <br>  /*VENTAS ANTERIORES AL PERIODO */<br>      SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>      AND RECEIPT.STORE=RECLINE.STORE AND  DTE &lt; :dte1<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :units_sold_before ;<br> <br> <br>      IF (:units_sold_before IS NULL) THEN<br>          units_sold_before=0;<br> <br> <br> <br> <br> /*VENTAS POSTERIORES AL PERIODO */<br> <br>    SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>      AND RECEIPT.STORE=RECLINE.STORE AND DTE &gt; :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :units_sold_after ;<br> <br> <br>      IF (:units_sold_after IS NULL) THEN<br> <br>          units_sold_after=0;<br> <br> <br> <br> <br> <br> <br> <br> /*VENTAS HECHAS FUERA DEL PERIODO */<br>      SELECT SUM(QUAN),SUM(EXTENDED),SUM(QUAN*COST)<br>      FROM RECLINE,RECEIPT  WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE<br>      AND DTE NOT BETWEEN :dte1 AND :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :TOTAL_UNITS_SOLD_OUT_OF_PERIOD,:SALES_OUT_OF_PERIOD,:COST_SOLD_OUT_OF_PERIOD;<br> <br> <br> <br> <br>      IF (TOTAL_UNITS_SOLD_OUT_OF_PERIOD IS NULL) THEN<br>      TOTAL_UNITS_SOLD_OUT_OF_PERIOD=0;<br> <br> <br>      IF (SALES_OUT_OF_PERIOD IS NULL) THEN<br>      SALES_OUT_OF_PERIOD=0;<br> <br> <br> <br> <br>      IF (COST_SOLD_OUT_OF_PERIOD IS NULL) THEN<br>      COST_SOLD_OUT_OF_PERIOD=0;<br> <br> <br> <br> <br>   END<br> <br> <br> <br>      SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE &lt; :dte1<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :rcvd_before,rcvd_before_cost ;<br> <br>      IF (:rcvd_before IS NULL) THEN<br>          BEGIN<br>          rcvd_before=0;<br> <br>          END<br> <br>     IF (:rcvd_before_cost IS NULL) THEN<br>          BEGIN<br>          rcvd_before_cost=0;<br> <br>          END<br> <br> <br> <br>      SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE &gt; :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :rcvd_after,rcvd_after_cost ;<br> <br>      IF (:rcvd_after IS NULL) THEN<br>          BEGIN<br>          rcvd_after=0;<br> <br>          END<br> <br>      IF (:rcvd_after_cost IS NULL) THEN<br>          BEGIN<br>          rcvd_after_cost=0;<br> <br>          END<br> <br>      SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND TRAN_NUM IS NULL AND QUAN&gt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :rcvd_period,:rcvd_period_cost ;<br> <br>      IF (:rcvd_period IS NULL) THEN<br>          BEGIN<br>          rcvd_period=0;<br> <br>          END<br>      IF (:rcvd_period_cost IS NULL) THEN<br>          BEGIN<br>          rcvd_period_cost=0;<br> <br>          END<br> <br>      SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND TRAN_NUM IS NULL AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :bo_period,bo_period_cost ;<br>      IF (:bo_period IS NULL) THEN<br>          BEGIN<br>          bo_period=0;<br>          END<br> <br>      IF (:bo_period_cost IS NULL) THEN<br>          BEGIN<br>          bo_period_cost=0;<br>          END<br> <br>      SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND PO_NUM IS NULL AND QUAN&gt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :tran_in_period,tran_in_period_cost ;<br> <br>      IF (:tran_in_period IS NULL) THEN<br>          BEGIN<br>          tran_in_period=0;<br> <br>          END<br>      IF (:tran_in_period_cost IS NULL) THEN<br>          BEGIN<br>          tran_in_period_cost=0;<br> <br>          END<br> <br> <br>      SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND PO_NUM IS NULL AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :TRAN_OUT_PERIOD,:TRAN_OUT_PERIOD_COST ;<br>      IF (:TRAN_OUT_PERIOD IS NULL) THEN<br>          BEGIN<br>          TRAN_OUT_PERIOD=0;<br>          END<br> <br>       IF (:TRAN_OUT_PERIOD_COST IS NULL) THEN<br>          BEGIN<br>          TRAN_OUT_PERIOD_COST=0;<br>          END<br>      BEGIN<br> <br> <br>         CUM_RCVD =rcvd_before+rcvd_after +rcvd_period + tran_in_period +bo_period+TRAN_OUT_PERIOD;<br>         CUM_SOLD =TOTAL_UNITS_SOLD_OUT_OF_PERIOD+TOTAL_UNITS_SOLD_PERIOD;<br>         CUM_COST_SOLD=COST_SOLD_PERIOD +COST_SOLD_OUT_OF_PERIOD;<br> <br> <br> <br>         INVENTARIO_INICIAL=rcvd_before-units_sold_before;<br>         INVENTARIO_FINAL=INVENTARIO_INICIAL +tran_in_period+rcvd_period-refunds_period+bo_period+TRAN_OUT_PERIOD-UNITS_SOLD_PERIOD;<br> <br> <br> <br> <br> <br>         INVENTORY_COST=OH*AVG_COST;<br>         INVENTORY_PRICE=OH*PRICE;<br>         UNITS_SOLD_HISTORY=CUM_SOLD;<br>         SALES_HISTORY=SALES_PERIOD+SALES_OUT_OF_PERIOD;<br> <br>      END<br>      SUSPEND;<br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_14"></a>
<div class="caption1">GET_INVT_DETAIL_PERIOD</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;GET_INVT_DETAIL_PERIOD&quot; (&quot;STORE_NUM&quot; Integer,&quot;DTE1&quot; Date,&quot;DTE2&quot; Date,&quot;BRAND1&quot; VarChar(30),&quot;STYLE1&quot; VarChar(30),&quot;EXT1&quot; VarChar(8)) RETURNS (&quot;RCVD_PERIOD&quot; Integer,&quot;RCVD_PERIOD_COST&quot; Double precision,&quot;TRAN_IN_PERIOD&quot; Integer,&quot;TRAN_IN_PERIOD_COST&quot; Double precision,&quot;BO_PERIOD&quot; Integer,&quot;BO_PERIOD_COST&quot; Double precision,&quot;TRAN_OUT_PERIOD&quot; Integer,&quot;TRAN_OUT_PERIOD_COST&quot; Double precision,&quot;UNITS_SOLD_PERIOD&quot; Integer,&quot;REFUNDS_PERIOD&quot; Integer,&quot;TOTAL_UNITS_SOLD_PERIOD&quot; Integer,&quot;COST_SOLD_PERIOD&quot; Double precision,&quot;SALES_PERIOD&quot; Double precision,&quot;LAYAWAY_PERIOD&quot; Integer,&quot;LAYAWAY_PERIOD_COST&quot; Integer,&quot;INVENTARIO_INICIAL&quot; Double precision,&quot;INVENTARIO_FINAL&quot; Double precision,&quot;COST_PERIOD&quot; Double precision,&quot;RCVD_BEFORE&quot; Integer,&quot;RCVD_BEFORE_COST&quot; Double precision,&quot;UNITS_SOLD_BEFORE&quot; Integer,&quot;SOLD_BEFORE_COST&quot; Double precision,&quot;INTRANSIT_PERIOD&quot; Integer,&quot;COST_INTRANSIT_PERIOD&quot; Double precision,&quot;COST_INITIAL_PERIOD&quot; Double precision,&quot;COST_END_PERIOD&quot; Double precision,&quot;DTE_START&quot; Date,&quot;DTE_END&quot; Date,&quot;AVG_COST&quot; Double precision)<br> AS<br> <br> <br> DECLARE VARIABLE PICKUP_DEDUCTIVE VARCHAR(5);<br> DECLARE VARIABLE DTE_INICIO DATE;<br> <br> <br> BEGIN<br>      DTE_START= :DTE1;<br>      DTE_END= :DTE2;<br> <br>      SELECT MAX(AVG_COST) FROM INVT WHERE STYLE= :style1 AND BRAND= :brand1<br>      AND EXT=:ext1 AND STORE= :store_num INTO :AVG_COST;<br> <br> <br>       SELECT sum(quan-quan_rcvd),sum(cost*(quan-quan_rcvd)) from TOLINE where closed=0 AND (quan-quan_rcvd)&lt;&gt;0<br>       AND DTE BETWEEN :DTE1 AND :DTE2 AND store=:STORE_NUM AND STYLE= :style1 AND BRAND= :brand1<br>       AND EXT=:ext1 INTO :INTRANSIT_PERIOD,:COST_INTRANSIT_PERIOD;<br> <br>       IF (:INTRANSIT_PERIOD is NULL) THEN<br>          BEGIN<br>          INTRANSIT_PERIOD=0;<br>          COST_INTRANSIT_PERIOD=0;<br>          END<br> <br> <br> <br>          /* UNIDADES VENDIDAS PERIODO*/<br>      SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE<br>      AND DTE BETWEEN :dte1 AND :dte2  AND QUAN&gt;0 AND STYLE= :style1  AND BRAND= :brand1 and<br>      STORE= :store_num AND EXT=:ext1 AND (TYP=0 OR (TYP&lt;&gt;0 AND CLOSED=1)) INTO :UNITS_SOLD_PERIOD;<br> <br> <br>          IF (:UNITS_SOLD_PERIOD IS NULL) THEN<br>             BEGIN<br>               UNITS_SOLD_PERIOD=0;<br>             END<br> <br> <br>           /* DEVOLUCIONES DE CLIENTE */<br>      SELECT SUM(QUAN) FROM RECLINE WHERE DTE BETWEEN :dte1 AND :dte2  AND QUAN&lt; 0 AND STYLE= :style1<br>      AND BRAND= :brand1 and store= :store_num  AND EXT=:ext1 INTO :REFUNDS_PERIOD ;<br>      IF (:REFUNDS_PERIOD IS NULL) THEN<br>              BEGIN<br>                REFUNDS_PERIOD=0;<br>              END<br> <br> <br>          /* TOTAL VENTAS PERIODO*/<br>          SELECT SUM(QUAN),SUM(EXTENDED),SUM(QUAN*COST)<br>          FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1<br>          AND :dte2 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num  AND EXT=:ext1 AND<br>          (TYP=0 OR (TYP&lt;&gt;0 AND CLOSED=1)) INTO :TOTAL_UNITS_SOLD_PERIOD,:SALES_PERIOD,:COST_SOLD_PERIOD;<br> <br> <br>           IF (:TOTAL_UNITS_SOLD_PERIOD IS NULL) THEN<br>               BEGIN<br>                TOTAL_UNITS_SOLD_PERIOD=0;<br>               END<br> <br>            IF (:SALES_PERIOD IS NULL) THEN<br>                BEGIN<br>                  SALES_PERIOD=0;<br>                END<br> <br>            IF (:COST_SOLD_PERIOD IS NULL) THEN<br>               BEGIN<br>                 COST_SOLD_PERIOD=0;<br>               END<br> <br> <br> <br> <br>            /*VENTAS ANTERIORES AL PERIODO */<br>            SELECT SUM(QUAN),SUM(QUAN*COST) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE<br>            AND  DTE &lt; :dte1 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num AND EXT=:ext1 AND<br>            (TYP=0 OR (TYP&lt;&gt;0 AND CLOSED=1))  INTO :UNITS_SOLD_BEFORE, :SOLD_BEFORE_COST ;<br> <br> <br>            IF (:UNITS_SOLD_BEFORE IS NULL) THEN<br>              BEGIN<br>                  UNITS_SOLD_BEFORE=0;<br>                  SOLD_BEFORE_COST=0;<br>              END<br> <br> <br> <br> <br> <br> <br>    /*APARTADOS ABIERTOS */<br> <br> <br>                SELECT SUM(QUAN),SUM(QUAN*COST) FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND<br>                RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 and :dte2  AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>                AND EXT=:ext1 AND RECEIPT.TYP=1 AND RECEIPT.CLOSED=0  INTO :LAYAWAY_PERIOD,:LAYAWAY_PERIOD_COST ;<br>                IF (:LAYAWAY_PERIOD IS NULL) THEN<br>                     BEGIN<br>                       LAYAWAY_PERIOD=0;<br>                       LAYAWAY_PERIOD_COST=0;<br>                     END<br> <br> <br> <br> <br>           SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE &lt; :dte1  AND STYLE= :style1 AND BRAND= :brand1<br>           and store= :store_num  AND EXT=:ext1 INTO :RCVD_BEFORE,RCVD_BEFORE_COST ;<br> <br>           IF (:RCVD_BEFORE IS NULL) THEN<br>             BEGIN<br>                     RCVD_BEFORE=0;<br>                     RCVD_BEFORE_COST=0;<br> <br>            END<br> <br> <br> <br>            SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2  AND TRAN_NUM IS NULL<br>            AND QUAN&gt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>            AND EXT=:ext1   INTO :rcvd_period,:rcvd_period_cost ;<br> <br>             IF (:rcvd_period IS NULL) THEN<br>                BEGIN<br>                  rcvd_period=0;<br>                  rcvd_period_cost=0;<br>                END<br> <br> <br>           SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>           AND TRAN_NUM IS NULL AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>           AND EXT=:ext1  INTO :bo_period,bo_period_cost ;<br>            IF (:bo_period IS NULL) THEN<br>              BEGIN<br>              bo_period=0;<br>              bo_period_cost=0;<br>              END<br> <br>          SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>          AND PO_NUM IS NULL AND QUAN&gt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>          AND EXT=:ext1  INTO :tran_in_period,tran_in_period_cost ;<br> <br>          IF (:tran_in_period IS NULL) THEN<br>          BEGIN<br>          tran_in_period=0;<br>          tran_in_period_cost=0;<br> <br>          END<br> <br> <br> <br>          SELECT SUM(QUAN),SUM(QUAN*COST) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>          AND PO_NUM IS NULL AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>          AND EXT=:ext1  INTO :TRAN_OUT_PERIOD,:TRAN_OUT_PERIOD_COST ;<br>          IF (:TRAN_OUT_PERIOD IS NULL) THEN<br>           BEGIN<br>              TRAN_OUT_PERIOD=0;<br>              TRAN_OUT_PERIOD_COST=0;<br>           END<br> <br> <br> <br>          BEGIN<br> <br>         COST_PERIOD=TRAN_IN_PERIOD_COST+TRAN_OUT_PERIOD_COST+bo_period_cost+rcvd_period_COST-COST_SOLD_PERIOD;<br>         INVENTARIO_INICIAL= :rcvd_before -:units_sold_before;<br>         INVENTARIO_FINAL=INVENTARIO_INICIAL +tran_in_period+rcvd_period-refunds_period+bo_period+TRAN_OUT_PERIOD-UNITS_SOLD_PERIOD;<br>         COST_INITIAL_PERIOD=RCVD_BEFORE_COST-SOLD_BEFORE_COST;<br>         COST_END_PERIOD=COST_INITIAL_PERIOD+COST_PERIOD;<br> <br> <br>         END<br>      SUSPEND;<br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_4"></a>
<div class="caption1">GET_STYLE</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;GET_STYLE&quot; (&quot;DTE1&quot; Date,&quot;DTE2&quot; Date) RETURNS (&quot;STORE&quot; Integer,&quot;BRAND&quot; VarChar(30),&quot;STYLE&quot; VarChar(30),&quot;EXT&quot; VarChar(8),&quot;DESCRIPTION&quot; VarChar(30),&quot;DEPT&quot; VarChar(15),&quot;TYP&quot; VarChar(15),&quot;SUBTYPE&quot; VarChar(15),&quot;SEASON&quot; VarChar(15),&quot;PRICE_PT&quot; VarChar(15),&quot;GENDER&quot; VarChar(15),&quot;OH&quot; Integer,&quot;CUM_RCVD&quot; Integer,&quot;CUM_SOLD&quot; Integer,&quot;RCVD_PERIOD&quot; Integer,&quot;TRAN_IN_PERIOD&quot; Integer,&quot;BO_PERIOD&quot; Integer,&quot;TRAN_OUT_PERIOD&quot; Integer,&quot;UNITS_SOLD_PERIOD&quot; Integer,&quot;REFUNDS_PERIOD&quot; Integer,&quot;INTRANSIT&quot; Integer,&quot;COST_INTRANSIT&quot; Double precision,&quot;TOTAL_UNITS_SOLD_PERIOD&quot; Integer,&quot;COST_SOLD_PERIOD&quot; Double precision,&quot;SALES_ORIG_PRICE_PERIOD&quot; Double precision,&quot;SALES_PERIOD&quot; Double precision,&quot;DISC_PERIOD&quot; Double precision,&quot;PROFIT_PERIOD&quot; Double precision,&quot;PROFIT_MARGIN_PERIOD&quot; Double precision,&quot;PERC_SOLD_PERIOD&quot; Double precision,&quot;TOTAL_UNITS_SOLD_OUT_OF_PERIOD&quot; Integer,&quot;DISC_OUT_OF_PERIOD&quot; Double precision,&quot;PROFIT_OUT_OF_PERIOD&quot; Double precision,&quot;SALES_ORIG_PRICE_OUT_OF_PERIOD&quot; Double precision,&quot;SALES_OUT_OF_PERIOD&quot; Double precision,&quot;COST_SOLD_OUT_OF_PERIOD&quot; Double precision,&quot;LAST_COST&quot; Double precision,&quot;AVG_COST&quot; Double precision,&quot;PRICE&quot; Double precision,&quot;PORC_EXIST_PERIOD&quot; Double precision,&quot;INVENTARIO_INICIAL&quot; Double precision,&quot;INVENTARIO_FINAL&quot; Double precision,&quot;INVENTARIO_PROMEDIO&quot; Double precision,&quot;DIAS_PERIODO&quot; Integer,&quot;ROTACION&quot; Double precision,&quot;FIRST_RCVD&quot; Date,&quot;FIRST_PRICE&quot; Double precision,&quot;LAST_RCVD&quot; Date,&quot;LAST_SOLD&quot; Date,&quot;ANTIQUITY&quot; Integer,&quot;DIAS_VENTA&quot; Double precision,&quot;RCVD_BEFORE&quot; Integer,&quot;UNITS_SOLD_BEFORE&quot; Integer,&quot;RCVD_AFTER&quot; Integer,&quot;UNITS_SOLD_AFTER&quot; Integer,&quot;INVENTORY_COST&quot; Double precision,&quot;INVENTORY_PRICE&quot; Double precision,&quot;UNITS_SOLD_HISTORY&quot; Integer,&quot;SALES_HISTORY&quot; Double precision,&quot;CUM_DISCOUNT&quot; Double precision,&quot;CUM_PROFIT&quot; Double precision,&quot;CUM_PROFIT_MARGIN&quot; Double precision,&quot;CUM_COST_SOLD&quot; Double precision,&quot;CUM_SOLD_ORIG_PRICE&quot; Double precision,&quot;PO_PERIOD&quot; Integer,&quot;PO_CUM&quot; Integer,&quot;INTRANSIT_PERIOD&quot; Integer,&quot;COST_INTRANSIT_PERIOD&quot; Double precision)<br> AS<br> <br> BEGIN<br> <br> <br>        FOR SELECT style,brand,dept,typ,season,ext,price_pt,gender,subtype,store FROM SKU_PROCEDURE ORDER BY BRAND,STYLE INTO :style,:brand,:dept,:typ,:season,:ext,:price_pt,:gender,:subtype,:store<br>        DO<br>           BEGIN<br>           EXECUTE PROCEDURE GET_STYLE_DETAIL (:store,:dte1,:dte2,:brand,:style,:ext)<br>           returning_values (:DESCRIPTION,:OH,:CUM_RCVD,:CUM_SOLD,:RCVD_PERIOD,:TRAN_IN_PERIOD,:BO_PERIOD,:TRAN_OUT_PERIOD,:UNITS_SOLD_PERIOD,<br>           :REFUNDS_PERIOD,:INTRANSIT ,:COST_INTRANSIT,:TOTAL_UNITS_SOLD_PERIOD,:COST_SOLD_PERIOD,:SALES_ORIG_PRICE_PERIOD,:SALES_PERIOD,:DISC_PERIOD,:PROFIT_PERIOD,:PROFIT_MARGIN_PERIOD,:PERC_SOLD_PERIOD,:TOTAL_UNITS_SOLD_OUT_OF_PERIOD,<br>           :DISC_OUT_OF_PERIOD,:PROFIT_OUT_OF_PERIOD,:SALES_ORIG_PRICE_OUT_OF_PERIOD,:SALES_OUT_OF_PERIOD,:COST_SOLD_OUT_OF_PERIOD,:LAST_COST,:AVG_COST,:PRICE,:PORC_EXIST_PERIOD,<br>           :INVENTARIO_INICIAL,:INVENTARIO_FINAL,:INVENTARIO_PROMEDIO,:DIAS_PERIODO,:ROTACION,:FIRST_RCVD,:FIRST_PRICE,:LAST_RCVD,<br>           :LAST_SOLD,:ANTIQUITY,:DIAS_VENTA,:RCVD_BEFORE,:UNITS_SOLD_BEFORE,:RCVD_AFTER,:UNITS_SOLD_AFTER,:INVENTORY_COST,:INVENTORY_PRICE,<br>           :UNITS_SOLD_HISTORY,:SALES_HISTORY,:CUM_DISCOUNT,:CUM_PROFIT,:CUM_PROFIT_MARGIN,CUM_COST_SOLD,:CUM_SOLD_ORIG_PRICE,:PO_PERIOD,:PO_CUM,:INTRANSIT_PERIOD,:COST_INTRANSIT_PERIOD);<br>           SUSPEND;<br>           END<br> <br> <br> <br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_1"></a>
<div class="caption1">GET_STYLE_DETAIL</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;GET_STYLE_DETAIL&quot; (&quot;STORE_NUM&quot; Integer,&quot;DTE1&quot; Date,&quot;DTE2&quot; Date,&quot;BRAND1&quot; VarChar(30),&quot;STYLE1&quot; VarChar(30),&quot;EXT1&quot; VarChar(8)) RETURNS (&quot;DESCRIPTION&quot; VarChar(30),&quot;OH&quot; Integer,&quot;CUM_RCVD&quot; Integer,&quot;CUM_SOLD&quot; Integer,&quot;RCVD_PERIOD&quot; Integer,&quot;TRAN_IN_PERIOD&quot; Integer,&quot;BO_PERIOD&quot; Integer,&quot;TRAN_OUT_PERIOD&quot; Integer,&quot;UNITS_SOLD_PERIOD&quot; Integer,&quot;REFUNDS_PERIOD&quot; Integer,&quot;INTRANSIT&quot; Integer,&quot;COST_INTRANSIT&quot; Double precision,&quot;TOTAL_UNITS_SOLD_PERIOD&quot; Integer,&quot;COST_SOLD_PERIOD&quot; Double precision,&quot;SALES_ORIG_PRICE_PERIOD&quot; Double precision,&quot;SALES_PERIOD&quot; Double precision,&quot;DISC_PERIOD&quot; Double precision,&quot;PROFIT_PERIOD&quot; Double precision,&quot;PROFIT_MARGIN_PERIOD&quot; Double precision,&quot;PERC_SOLD_PERIOD&quot; Double precision,&quot;TOTAL_UNITS_SOLD_OUT_OF_PERIOD&quot; Integer,&quot;DISC_OUT_OF_PERIOD&quot; Double precision,&quot;PROFIT_OUT_OF_PERIOD&quot; Double precision,&quot;SALES_ORIG_PRICE_OUT_OF_PERIOD&quot; Double precision,&quot;SALES_OUT_OF_PERIOD&quot; Double precision,&quot;COST_SOLD_OUT_OF_PERIOD&quot; Double precision,&quot;LAST_COST&quot; Double precision,&quot;AVG_COST&quot; Double precision,&quot;PRICE&quot; Double precision,&quot;PORC_EXIST_PERIOD&quot; Double precision,&quot;INVENTARIO_INICIAL&quot; Double precision,&quot;INVENTARIO_FINAL&quot; Double precision,&quot;INVENTARIO_PROMEDIO&quot; Double precision,&quot;DIAS_PERIODO&quot; Integer,&quot;ROTACION&quot; Double precision,&quot;FIRST_RCVD&quot; Date,&quot;FIRST_PRICE&quot; Double precision,&quot;LAST_RCVD&quot; Date,&quot;LAST_SOLD&quot; Date,&quot;ANTIQUITY&quot; Integer,&quot;DIAS_VENTA&quot; Double precision,&quot;RCVD_BEFORE&quot; Integer,&quot;UNITS_SOLD_BEFORE&quot; Integer,&quot;RCVD_AFTER&quot; Integer,&quot;UNITS_SOLD_AFTER&quot; Integer,&quot;INVENTORY_COST&quot; Double precision,&quot;INVENTORY_PRICE&quot; Double precision,&quot;UNITS_SOLD_HISTORY&quot; Integer,&quot;SALES_HISTORY&quot; Double precision,&quot;CUM_DISCOUNT&quot; Double precision,&quot;CUM_PROFIT&quot; Double precision,&quot;CUM_PROFIT_MARGIN&quot; Double precision,&quot;CUM_COST_SOLD&quot; Double precision,&quot;CUM_SOLD_ORIG_PRICE&quot; Double precision,&quot;PO_PERIOD&quot; Integer,&quot;PO_CUM&quot; Integer,&quot;INTRANSIT_PERIOD&quot; Integer,&quot;COST_INTRANSIT_PERIOD&quot; Double precision)<br> AS<br> <br> DECLARE VARIABLE units_sold_period_typ_0 INTEGER;<br> DECLARE VARIABLE units_sold_period_typ_1 INTEGER;<br> DECLARE VARIABLE TOTAL_UNITS_SOLD_PERIOD_TYP_0 INTEGER;<br> DECLARE VARIABLE SALES_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE SALES_ORIG_PRICE_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE PROFIT_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE DISC_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE COST_SOLD_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_UNITS_SOLD_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE SALES_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE SALES_ORIG_PRICE_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE PROFIT_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE DISC_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE COST_SOLD_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_BEFORE_TYP_0 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_BEFORE_TYP_1 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_AFTER_TYP_0 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_AFTER_TYP_1 INTEGER;<br> DECLARE VARIABLE UNITS_SOLD_OUT_OF_PERIOD_TYP_0 INTEGER;<br> DECLARE VARIABLE TOTAL_SOLD_OUT_OF_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE PROFIT_OUT_OF_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE DISC_OUT_OF_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0 DOUBLE PRECISION;<br> DECLARE VARIABLE UNITS_SOLD_OUT_OF_PERIOD_TYP_1 INTEGER;<br> DECLARE VARIABLE TOTAL_SOLD_OUT_OF_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE PROFIT_OUT_OF_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE DISC_OUT_OF_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1 DOUBLE PRECISION;<br> DECLARE VARIABLE PICKUP_DEDUCTIVE VARCHAR(5);<br> <br> <br> <br>  BEGIN<br> <br> <br>   SELECT OPTIONVALUE FROM STOREOPTIONS WHERE ID=144 AND STORE=:STORE_NUM INTO :PICKUP_DEDUCTIVE;<br> <br> <br>   /* INVENTARIO*/<br>     SELECT SUM(QUAN),MIN(FIRST_RCVD),MAX(LAST_RCVD),MAX(LAST_SOLD),MAX(PRICE),MAX(LAST_COST),MAX(AVG_COST),<br>     MAX(FIRST_PRICE),DESCRIPTION,SUM(INTRANSIT)  FROM INVT WHERE STYLE= :style1 AND BRAND= :brand1<br>     and store= :store_num  AND EXT=:ext1 GROUP BY DESCRIPTION<br>     INTO :oh,:first_rcvd,:last_rcvd,:last_sold,:price,:last_cost,:avg_cost,:first_price,:description,:intransit;<br>     IF (:FIRST_RCVD IS NULL OR :oh=0) THEN<br>       BEGIN<br>       ANTIQUITY=0;<br>       END<br>     ELSE<br>     ANTIQUITY=CURRENT_DATE-:first_rcvd;<br> <br>     IF (:avg_cost is NULL OR :intransit is NULL) THEN<br>      BEGIN<br>       COST_INTRANSIT=0;<br>      END<br>     ELSE<br>         BEGIN<br>         COST_INTRANSIT=:intransit*:avg_cost;<br>         END<br> <br> <br>   SELECT sum(quan-quan_rcvd),sum(cost*(quan-quan_rcvd)) from TOLINE where closed=0 AND (quan-quan_rcvd)&lt;&gt;0<br>   AND DTE BETWEEN :DTE1 AND :DTE2 AND store=:STORE_NUM AND STYLE= :style1 AND BRAND= :brand1<br>   AND STORE= :store_num  AND EXT=:ext1 INTO :INTRANSIT_PERIOD,:COST_INTRANSIT_PERIOD;<br> <br>   IF (:INTRANSIT_PERIOD is NULL) THEN<br>      BEGIN<br>       INTRANSIT_PERIOD=0;<br>      END<br> <br>   IF (:COST_INTRANSIT_PERIOD is NULL) THEN<br>      BEGIN<br>       COST_INTRANSIT_PERIOD=0;<br>      END<br> <br> <br>   IF (:PICKUP_DEDUCTIVE='True' ) THEN<br>   BEGIN<br> <br>              /* UNIDADES VENDIDAS PERIODO*/<br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND<br>               RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>               AND QUAN&gt;0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP=0 INTO :units_sold_period_typ_0;<br> <br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND<br>               RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>               AND QUAN&gt;0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP&lt;&gt;0 AND RECEIPT.CLOSED=1 INTO :units_sold_period_typ_1;<br> <br>               IF (:units_sold_period_typ_0 IS NULL) THEN<br>                  units_sold_period_typ_0=0;<br> <br>               IF (:units_sold_period_typ_1 IS NULL) THEN<br>                  units_sold_period_typ_1=0;<br>               units_sold_period=:units_sold_period_typ_0+:units_sold_period_typ_1;<br> <br>           /* DEVOLUCIONES DE CLIENTE */<br>              SELECT SUM(QUAN) FROM RECLINE WHERE DTE BETWEEN :dte1 AND :dte2<br>              AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>              AND EXT=:ext1 INTO :refunds_period ;<br>              IF (:refunds_period IS NULL) THEN<br>                  refunds_period=0;<br> <br>          /* TOTAL VENTAS PERIODO*/<br>             SELECT SUM(QUAN),SUM(QUAN*ORIG_PRICE),SUM(EXTENDED),SUM(TOTAL_PROFIT),SUM(REG_MKDWN),SUM(QUAN*COST)<br>             FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>             AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>             AND EXT=:ext1 AND RECEIPT.TYP=0<br>             INTO :TOTAL_UNITS_SOLD_PERIOD_TYP_0,:SALES_ORIG_PRICE_PERIOD_TYP_0,:sales_period_typ_0,:profit_period_typ_0,:disc_period_typ_0,:cost_sold_period_typ_0 ;<br> <br>            /* TOTAL VENTAS PERIODO*/<br>            SELECT SUM(QUAN),SUM(QUAN*ORIG_PRICE),SUM(EXTENDED),SUM(TOTAL_PROFIT),SUM(REG_MKDWN),SUM(QUAN*COST)<br>            FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>            AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>            AND EXT=:ext1 AND RECEIPT.TYP&lt;&gt;0 AND RECEIPT.CLOSED=1<br>            INTO :TOTAL_UNITS_SOLD_PERIOD_TYP_1,:SALES_ORIG_PRICE_PERIOD_TYP_1,:sales_period_typ_1,:profit_period_typ_1,:disc_period_typ_1,:cost_sold_period_typ_1 ;<br> <br>            IF (:TOTAL_UNITS_SOLD_PERIOD_TYP_0 IS NULL) THEN<br>               TOTAL_UNITS_SOLD_PERIOD_TYP_0=0;<br>            IF (:TOTAL_UNITS_SOLD_PERIOD_TYP_1 IS NULL) THEN<br>               TOTAL_UNITS_SOLD_PERIOD_TYP_1=0;<br> <br>            IF (:sales_period_typ_0 IS NULL) THEN<br>                sales_period_typ_0=0;<br>            IF (:sales_period_typ_1 IS NULL) THEN<br>                sales_period_typ_1=0;<br> <br>            IF (:PROFIT_PERIOD_TYP_0 IS NULL) THEN<br>                PROFIT_PERIOD_TYP_0=0;<br>            IF (:PROFIT_PERIOD_TYP_1 IS NULL) THEN<br>                PROFIT_PERIOD_TYP_1=0;<br> <br>            IF (:DISC_PERIOD_TYP_0 IS NULL) THEN<br>               DISC_PERIOD_TYP_0=0;<br>            IF (:DISC_PERIOD_TYP_1 IS NULL) THEN<br>               DISC_PERIOD_TYP_1=0;<br>            IF (:COST_SOLD_PERIOD_TYP_0 IS NULL) THEN<br>               COST_SOLD_PERIOD_TYP_0=0;<br>            IF (:COST_SOLD_PERIOD_TYP_1 IS NULL) THEN<br>               COST_SOLD_PERIOD_TYP_1=0;<br> <br>            IF (:SALES_ORIG_PRICE_PERIOD_TYP_0 IS NULL) THEN<br>               SALES_ORIG_PRICE_PERIOD_TYP_0=0;<br> <br>            IF (:SALES_ORIG_PRICE_PERIOD_TYP_1 IS NULL) THEN<br>            SALES_ORIG_PRICE_PERIOD_TYP_1=0;<br> <br> <br>             TOTAL_UNITS_SOLD_PERIOD=:TOTAL_UNITS_SOLD_PERIOD_TYP_0+:TOTAL_UNITS_SOLD_PERIOD_TYP_1;<br>             SALES_PERIOD=:sales_period_typ_0 + :sales_period_typ_1;<br>             profit_period=:profit_period_typ_0 + :profit_period_typ_1;<br>             disc_period=:disc_period_typ_0 + :disc_period_typ_1;<br>             cost_sold_period=:cost_sold_period_typ_0+ :cost_sold_period_typ_1;<br>             SALES_ORIG_PRICE_PERIOD=:SALES_ORIG_PRICE_PERIOD_TYP_0 + :SALES_ORIG_PRICE_PERIOD_TYP_1;<br> <br>             IF (:sales_period=0) THEN<br>               BEGIN<br>               PROFIT_MARGIN_PERIOD=0;<br>               SALES_PERIOD=0;<br>               END<br>             ELSE<br>               PROFIT_MARGIN_PERIOD=(profit_period/sales_period) *100;<br> <br> <br>  /*VENTAS ANTERIORES AL PERIODO */<br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>               AND RECEIPT.STORE=RECLINE.STORE AND  DTE &lt; :dte1<br>               AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP=0      INTO :units_sold_before_typ_0 ;<br> <br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>               AND RECEIPT.STORE=RECLINE.STORE AND DTE &lt; :dte1<br>               AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>               AND EXT=:ext1 AND RECEIPT.TYP&lt;&gt;0 and receipt.closed=1<br>               INTO :units_sold_before_typ_1 ;<br>               IF (:units_sold_before_typ_0 IS NULL) THEN<br>                  units_sold_before_typ_0=0;<br> <br>               IF (:units_sold_before_typ_1 IS NULL) THEN<br>                   units_sold_before_typ_1=0;<br> <br>               units_sold_before=:units_sold_before_typ_0+:units_sold_before_typ_1;<br> <br>                /*VENTAS POSTERIORES AL PERIODO */<br> <br>               SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>                AND RECEIPT.STORE=RECLINE.STORE AND DTE &gt; :dte2<br>                AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>                AND EXT=:ext1 AND TYP=0 INTO :units_sold_after_typ_0 ;<br> <br>                SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>                AND RECEIPT.STORE=RECLINE.STORE AND DTE &gt; :dte2<br>                AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>                AND EXT=:ext1 AND TYP&lt;&gt;0 and receipt.closed=1<br>                INTO :units_sold_after_typ_1 ;<br>                 IF (:units_sold_after_typ_0 IS NULL) THEN<br>                     units_sold_after_typ_0=0;<br>                 IF (:units_sold_after_typ_1 IS NULL) THEN<br>                    units_sold_after_typ_1=0;<br> <br>                 units_sold_after=:units_sold_after_typ_0 + :units_sold_after_typ_1 ;<br> <br>   /*VENTAS HECHAS FUERA DEL PERIODO */<br>      SELECT SUM(QUAN),SUM(QUAN*ORIG_PRICE),SUM(EXTENDED),SUM(TOTAL_PROFIT),SUM(REG_MKDWN),SUM(QUAN*COST)<br>      FROM RECLINE,RECEIPT  WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND<br>      TYP=0 AND DTE NOT BETWEEN :dte1 AND :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :UNITS_SOLD_OUT_OF_PERIOD_TYP_0,:TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_0,:TOTAL_SOLD_OUT_OF_PERIOD_TYP_0,:PROFIT_OUT_OF_PERIOD_TYP_0,<br>      :DISC_OUT_OF_PERIOD_TYP_0,:TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0;<br> <br>      SELECT SUM(QUAN),SUM(QUAN*ORIG_PRICE),SUM(EXTENDED),SUM(TOTAL_PROFIT),SUM(REG_MKDWN),SUM(QUAN*COST)<br>      FROM RECLINE,RECEIPT  WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND<br>      TYP&lt;&gt;0 AND RECEIPT.CLOSED=1 AND DTE NOT BETWEEN :dte1 AND :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :UNITS_SOLD_OUT_OF_PERIOD_TYP_1,:TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_1,:TOTAL_SOLD_OUT_OF_PERIOD_TYP_1,:PROFIT_OUT_OF_PERIOD_TYP_1,<br>      :DISC_OUT_OF_PERIOD_TYP_1,:TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1;<br> <br> <br>      IF (UNITS_SOLD_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      UNITS_SOLD_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (UNITS_SOLD_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      UNITS_SOLD_OUT_OF_PERIOD_TYP_1=0;<br> <br>      IF (TOTAL_SOLD_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      TOTAL_SOLD_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (TOTAL_SOLD_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      TOTAL_SOLD_OUT_OF_PERIOD_TYP_1=0;<br> <br>      IF (PROFIT_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      PROFIT_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (PROFIT_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      PROFIT_OUT_OF_PERIOD_TYP_1=0;<br> <br>      IF (DISC_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      DISC_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (DISC_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      DISC_OUT_OF_PERIOD_TYP_1=0;<br> <br>      IF (TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0 IS NULL) THEN<br>      TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0=0;<br> <br>      IF (TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1 IS NULL) THEN<br>      TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1=0;<br> <br>      IF (:TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_0 IS NULL) THEN<br>      TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_0=0;<br> <br>      IF (:TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_1 IS NULL) THEN<br>      TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_1=0;<br> <br> <br>      TOTAL_UNITS_SOLD_OUT_OF_PERIOD=:UNITS_SOLD_OUT_OF_PERIOD_TYP_0 + :UNITS_SOLD_OUT_OF_PERIOD_TYP_1;<br>      SALES_OUT_OF_PERIOD=:TOTAL_SOLD_OUT_OF_PERIOD_TYP_0 +:TOTAL_SOLD_OUT_OF_PERIOD_TYP_1;<br>      PROFIT_OUT_OF_PERIOD=:PROFIT_OUT_OF_PERIOD_TYP_0+:PROFIT_OUT_OF_PERIOD_TYP_1;<br>      DISC_OUT_OF_PERIOD=:DISC_OUT_OF_PERIOD_TYP_0 + :DISC_OUT_OF_PERIOD_TYP_1;<br>      COST_SOLD_OUT_OF_PERIOD=:TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_0+ :TOTAL_COST_SOLD_OUT_OF_PERIOD_TYP_1;<br>      SALES_ORIG_PRICE_OUT_OF_PERIOD=TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_0+TOTAL_SOLD_ORIG_PRICE_PERIOD_TYP_1;<br> <br> <br>   END<br> <br>   ELSE<br> <br>   BEGIN<br> <br>    /* UNIDADES VENDIDAS PERIODO*/<br>    SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM AND<br>    RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>    AND QUAN&gt;0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>    AND EXT=:ext1<br>      INTO :units_sold_period;<br> <br> <br> <br> <br> <br>   IF (:units_sold_period IS NULL) THEN<br> <br>          units_sold_period=0;<br> <br> <br> <br> <br> <br>   /* DEVOLUCIONES DE CLIENTE */<br>      SELECT SUM(QUAN) FROM RECLINE WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :refunds_period ;<br>      IF (:refunds_period IS NULL) THEN<br> <br>          refunds_period=0;<br> <br> <br> <br> <br>  /* TOTAL VENTAS PERIODO*/<br>    SELECT SUM(QUAN),SUM(QUAN*ORIG_PRICE),SUM(EXTENDED),SUM(TOTAL_PROFIT),SUM(REG_MKDWN),SUM(QUAN*COST)<br>    FROM RECLINE,RECEIPT WHERE  RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE AND DTE BETWEEN :dte1 AND :dte2<br>    AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>    AND EXT=:ext1<br>      INTO :TOTAL_UNITS_SOLD_PERIOD,:SALES_ORIG_PRICE_PERIOD,:sales_period,:profit_period,:disc_period,:cost_sold_period ;<br> <br> <br>   IF (:TOTAL_UNITS_SOLD_PERIOD IS NULL) THEN<br>   TOTAL_UNITS_SOLD_PERIOD=0;<br> <br>   IF (:sales_period IS NULL) THEN<br>   sales_period=0;<br> <br> <br>   IF (:PROFIT_PERIOD IS NULL) THEN<br>   PROFIT_PERIOD=0;<br> <br> <br>   IF (:DISC_PERIOD IS NULL) THEN<br>   DISC_PERIOD=0;<br> <br> <br>   IF (:COST_SOLD_PERIOD IS NULL) THEN<br>   COST_SOLD_PERIOD=0;<br> <br> <br>  IF (:SALES_ORIG_PRICE_PERIOD IS NULL) THEN<br>   SALES_ORIG_PRICE_PERIOD=0;<br> <br> <br> <br> <br>    IF (:sales_period=0) THEN<br>            BEGIN<br>            PROFIT_MARGIN_PERIOD=0;<br>            SALES_PERIOD=0;<br>            END<br>     ELSE<br>           PROFIT_MARGIN_PERIOD=(profit_period/sales_period) *100;<br> <br> <br> <br> <br>  /*VENTAS ANTERIORES AL PERIODO */<br>      SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>      AND RECEIPT.STORE=RECLINE.STORE AND  DTE &lt; :dte1<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :units_sold_before ;<br> <br> <br>      IF (:units_sold_before IS NULL) THEN<br>          units_sold_before=0;<br> <br> <br> <br> <br> /*VENTAS POSTERIORES AL PERIODO */<br> <br>    SELECT SUM(QUAN) FROM RECLINE,RECEIPT WHERE RECEIPT.NUM=RECLINE.REC_NUM<br>      AND RECEIPT.STORE=RECLINE.STORE AND DTE &gt; :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :units_sold_after ;<br> <br> <br>      IF (:units_sold_after IS NULL) THEN<br> <br>          units_sold_after=0;<br> <br> <br> <br> <br> <br> <br> <br> /*VENTAS HECHAS FUERA DEL PERIODO */<br>      SELECT SUM(QUAN),SUM(QUAN*ORIG_PRICE),SUM(EXTENDED),SUM(TOTAL_PROFIT),SUM(REG_MKDWN),SUM(QUAN*COST)<br>      FROM RECLINE,RECEIPT  WHERE RECEIPT.NUM=RECLINE.REC_NUM AND RECEIPT.STORE=RECLINE.STORE<br>      AND DTE NOT BETWEEN :dte1 AND :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :TOTAL_UNITS_SOLD_OUT_OF_PERIOD,:SALES_ORIG_PRICE_OUT_OF_PERIOD,:SALES_OUT_OF_PERIOD,:PROFIT_OUT_OF_PERIOD,<br>      :DISC_OUT_OF_PERIOD,:COST_SOLD_OUT_OF_PERIOD;<br> <br> <br> <br> <br>      IF (TOTAL_UNITS_SOLD_OUT_OF_PERIOD IS NULL) THEN<br>      TOTAL_UNITS_SOLD_OUT_OF_PERIOD=0;<br> <br> <br>      IF (SALES_OUT_OF_PERIOD IS NULL) THEN<br>      SALES_OUT_OF_PERIOD=0;<br> <br> <br>      IF (PROFIT_OUT_OF_PERIOD IS NULL) THEN<br>      PROFIT_OUT_OF_PERIOD=0;<br> <br> <br>      IF (DISC_OUT_OF_PERIOD IS NULL) THEN<br>      DISC_OUT_OF_PERIOD=0;<br> <br> <br>      IF (COST_SOLD_OUT_OF_PERIOD IS NULL) THEN<br>      COST_SOLD_OUT_OF_PERIOD=0;<br> <br> <br>      IF (:SALES_ORIG_PRICE_OUT_OF_PERIOD IS NULL) THEN<br>      SALES_ORIG_PRICE_OUT_OF_PERIOD=0;<br> <br> <br> <br>   END<br> <br> <br> <br>      SELECT SUM(QUAN) FROM TRANSFER WHERE DTE &lt; :dte1<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :rcvd_before ;<br> <br>      IF (:rcvd_before IS NULL) THEN<br>          BEGIN<br>          rcvd_before=0;<br> <br>          END<br> <br> <br>      SELECT SUM(QUAN) FROM TRANSFER WHERE DTE &gt; :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :rcvd_after ;<br> <br>      IF (:rcvd_after IS NULL) THEN<br>          BEGIN<br>          rcvd_after=0;<br> <br>          END<br> <br> <br>      SELECT sum(quan-quan_rcvd) FROM POLINE,PO where POLINE.CLOSED=0 and PO.NUM=POLINE.PO_NUM<br>      AND PO.STORE_MADE=POLINE.STORE_MADE and PO.CLOSED=0 AND DTE BETWEEN :dte1 AND :dte2<br>      AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1 INTO :PO_PERIOD;<br> <br>      SELECT sum(quan-quan_rcvd) FROM POLINE,PO where POLINE.CLOSED=0 and PO.NUM=POLINE.PO_NUM<br>      AND PO.STORE_MADE=POLINE.STORE_MADE and PO.CLOSED=0 AND STYLE= :style1 AND BRAND= :brand1 AND<br>      store= :store_num  AND EXT=:ext1 INTO :PO_CUM;<br> <br> <br>      SELECT SUM(QUAN) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND TRAN_NUM IS NULL AND QUAN&gt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :rcvd_period ;<br> <br>      IF (:rcvd_period IS NULL) THEN<br>          BEGIN<br>          rcvd_period=0;<br> <br>          END<br> <br>      SELECT SUM(QUAN) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND TRAN_NUM IS NULL AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :bo_period ;<br>      IF (:bo_period IS NULL) THEN<br>          BEGIN<br>          bo_period=0;<br>          END<br> <br>      SELECT SUM(QUAN) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND PO_NUM IS NULL AND QUAN&gt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :tran_in_period ;<br> <br>      IF (:tran_in_period IS NULL) THEN<br>          BEGIN<br>          tran_in_period=0;<br> <br>          END<br> <br> <br>      SELECT SUM(QUAN) FROM TRANSFER WHERE DTE BETWEEN :dte1 AND :dte2<br>      AND PO_NUM IS NULL AND QUAN&lt; 0 AND STYLE= :style1 AND BRAND= :brand1 and store= :store_num<br>      AND EXT=:ext1<br>      INTO :TRAN_OUT_PERIOD ;<br>      IF (:TRAN_OUT_PERIOD IS NULL) THEN<br>          BEGIN<br>          TRAN_OUT_PERIOD=0;<br>          END<br>      BEGIN<br> <br> <br>         CUM_RCVD =rcvd_before+rcvd_after +rcvd_period + tran_in_period +bo_period+TRAN_OUT_PERIOD;<br>         CUM_SOLD =TOTAL_UNITS_SOLD_OUT_OF_PERIOD+TOTAL_UNITS_SOLD_PERIOD;<br>         CUM_COST_SOLD=COST_SOLD_PERIOD +COST_SOLD_OUT_OF_PERIOD;<br>         DIAS_PERIODO=(DTE2-DTE1)+1;<br> <br> <br>         INVENTARIO_INICIAL=rcvd_before-units_sold_before;<br>         INVENTARIO_FINAL=INVENTARIO_INICIAL +tran_in_period+rcvd_period-refunds_period+bo_period+TRAN_OUT_PERIOD-UNITS_SOLD_PERIOD;<br>         IF (INVENTARIO_FINAL+TOTAL_UNITS_SOLD_PERIOD &gt; 0) THEN<br>         PERC_SOLD_PERIOD=100 *CAST(TOTAL_UNITS_SOLD_PERIOD AS DOUBLE PRECISION)/(CAST(INVENTARIO_FINAL AS DOUBLE PRECISION) +CAST(TOTAL_UNITS_SOLD_PERIOD AS DOUBLE PRECISION));<br>         ELSE<br>         PERC_SOLD_PERIOD=0;<br> <br>         INVENTARIO_PROMEDIO= CAST((INVENTARIO_FINAL+INVENTARIO_INICIAL) AS DOUBLE PRECISION)/2.00;<br>         IF (INVENTARIO_PROMEDIO&gt; 0 AND DIAS_PERIODO&gt;0) THEN<br>         ROTACION=(CAST(TOTAL_UNITS_SOLD_PERIOD AS DOUBLE PRECISION)/INVENTARIO_PROMEDIO)* CAST(365  AS DOUBLE PRECISION)/CAST(DIAS_PERIODO  AS DOUBLE PRECISION);<br>         ELSE<br>         ROTACION=0;<br> <br> <br> <br> <br> <br>         IF (TOTAL_UNITS_SOLD_PERIOD&lt;&gt;0)  THEN<br>           DIAS_VENTA=CAST(INVENTARIO_FINAL AS DOUBLE PRECISION)*(CAST(DIAS_PERIODO AS DOUBLE PRECISION) / CAST(TOTAL_UNITS_SOLD_PERIOD AS DOUBLE PRECISION));<br>         ELSE<br>          DIAS_VENTA=0;<br> <br>         INVENTORY_COST=OH*AVG_COST;<br>         INVENTORY_PRICE=OH*PRICE;<br>         UNITS_SOLD_HISTORY=CUM_SOLD;<br>         SALES_HISTORY=SALES_PERIOD+SALES_OUT_OF_PERIOD;<br>         CUM_DISCOUNT=DISC_PERIOD+DISC_OUT_OF_PERIOD;<br>         CUM_PROFIT=PROFIT_PERIOD+PROFIT_OUT_OF_PERIOD;<br>         CUM_SOLD_ORIG_PRICE=SALES_ORIG_PRICE_PERIOD + SALES_ORIG_PRICE_OUT_OF_PERIOD ;<br>         IF (SALES_HISTORY&gt;0) THEN<br>         CUM_PROFIT_MARGIN=(CUM_PROFIT/SALES_HISTORY)*100;<br>         ELSE<br>         CUM_PROFIT_MARGIN=0;<br> <br>      END<br>      SUSPEND;<br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_18"></a>
<div class="caption1">GET_STYLE_INVT</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;GET_STYLE_INVT&quot; (&quot;DTE1&quot; Date,&quot;DTE2&quot; Date) RETURNS (&quot;STORE&quot; Integer,&quot;BRAND&quot; VarChar(30),&quot;STYLE&quot; VarChar(30),&quot;EXT&quot; VarChar(8),&quot;DESCRIPTION&quot; VarChar(30),&quot;DEPT&quot; VarChar(15),&quot;TYP&quot; VarChar(15),&quot;SUBTYPE&quot; VarChar(15),&quot;SEASON&quot; VarChar(15),&quot;PRICE_PT&quot; VarChar(15),&quot;GENDER&quot; VarChar(15),&quot;OH&quot; Integer,&quot;CUM_RCVD&quot; Integer,&quot;CUM_SOLD&quot; Integer,&quot;RCVD_PERIOD&quot; Integer,&quot;RCVD_PERIOD_COST&quot; Double precision,&quot;TRAN_IN_PERIOD&quot; Integer,&quot;TRAN_IN_PERIOD_COST&quot; Double precision,&quot;BO_PERIOD&quot; Integer,&quot;BO_PERIOD_COST&quot; Double precision,&quot;TRAN_OUT_PERIOD&quot; Integer,&quot;TRAN_OUT_PERIOD_COST&quot; Double precision,&quot;UNITS_SOLD_PERIOD&quot; Integer,&quot;REFUNDS_PERIOD&quot; Integer,&quot;INTRANSIT&quot; Integer,&quot;COST_INTRANSIT&quot; Double precision,&quot;TOTAL_UNITS_SOLD_PERIOD&quot; Integer,&quot;COST_SOLD_PERIOD&quot; Double precision,&quot;SALES_PERIOD&quot; Double precision,&quot;TOTAL_UNITS_SOLD_OUT_OF_PERIOD&quot; Integer,&quot;SALES_OUT_OF_PERIOD&quot; Double precision,&quot;COST_SOLD_OUT_OF_PERIOD&quot; Double precision,&quot;AVG_COST&quot; Double precision,&quot;PRICE&quot; Double precision,&quot;INVENTARIO_INICIAL&quot; Double precision,&quot;INVENTARIO_FINAL&quot; Double precision,&quot;RCVD_BEFORE&quot; Integer,&quot;RCVD_BEFORE_COST&quot; Double precision,&quot;UNITS_SOLD_BEFORE&quot; Integer,&quot;RCVD_AFTER&quot; Integer,&quot;RCVD_AFTER_COST&quot; Double precision,&quot;UNITS_SOLD_AFTER&quot; Integer,&quot;INVENTORY_COST&quot; Double precision,&quot;INVENTORY_PRICE&quot; Double precision,&quot;UNITS_SOLD_HISTORY&quot; Integer,&quot;SALES_HISTORY&quot; Double precision,&quot;CUM_COST_SOLD&quot; Double precision,&quot;INTRANSIT_PERIOD&quot; Integer,&quot;COST_INTRANSIT_PERIOD&quot; Double precision)<br> AS<br> <br> BEGIN<br> <br> <br>        FOR SELECT style,brand,dept,typ,season,ext,price_pt,gender,subtype,store FROM SKU_PROCEDURE ORDER BY BRAND,STYLE INTO :style,:brand,:dept,:typ,:season,:ext,:price_pt,:gender,:subtype,:store<br>        DO<br>           BEGIN<br>           EXECUTE PROCEDURE GET_INVT_DETAIL (:store,:dte1,:dte2,:brand,:style,:ext)<br>           returning_values (:DESCRIPTION,:OH,:CUM_RCVD,:CUM_SOLD,:RCVD_PERIOD,:RCVD_PERIOD_COST,:TRAN_IN_PERIOD,:TRAN_IN_PERIOD_COST,:BO_PERIOD,BO_PERIOD_COST,:TRAN_OUT_PERIOD,:TRAN_OUT_PERIOD_COST,:UNITS_SOLD_PERIOD,<br>           :REFUNDS_PERIOD,:INTRANSIT ,:COST_INTRANSIT,:TOTAL_UNITS_SOLD_PERIOD,:COST_SOLD_PERIOD,:SALES_PERIOD,:TOTAL_UNITS_SOLD_OUT_OF_PERIOD,<br>           :SALES_OUT_OF_PERIOD,:COST_SOLD_OUT_OF_PERIOD,:AVG_COST,:PRICE,:INVENTARIO_INICIAL,:INVENTARIO_FINAL,:RCVD_BEFORE,:RCVD_BEFORE_COST,<br>           :UNITS_SOLD_BEFORE,:RCVD_AFTER,:RCVD_AFTER_COST,:UNITS_SOLD_AFTER,:INVENTORY_COST,:INVENTORY_PRICE,<br>           :UNITS_SOLD_HISTORY,:SALES_HISTORY,CUM_COST_SOLD,:INTRANSIT_PERIOD,:COST_INTRANSIT_PERIOD);<br>           SUSPEND;<br>           END<br> <br> <br> <br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_15"></a>
<div class="caption1">GET_STYLE_INVT_PERIOD</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;GET_STYLE_INVT_PERIOD&quot; (&quot;DTE1&quot; Date,&quot;DTE2&quot; Date) RETURNS (&quot;STORE&quot; Integer,&quot;BRAND&quot; VarChar(30),&quot;STYLE&quot; VarChar(30),&quot;EXT&quot; VarChar(8),&quot;DESCRIPTION&quot; VarChar(30),&quot;DEPT&quot; VarChar(15),&quot;TYP&quot; VarChar(15),&quot;SUBTYPE&quot; VarChar(15),&quot;SEASON&quot; VarChar(15),&quot;PRICE_PT&quot; VarChar(15),&quot;GENDER&quot; VarChar(15),&quot;RCVD_PERIOD&quot; Integer,&quot;RCVD_PERIOD_COST&quot; Double precision,&quot;TRAN_IN_PERIOD&quot; Integer,&quot;TRAN_IN_PERIOD_COST&quot; Double precision,&quot;BO_PERIOD&quot; Integer,&quot;BO_PERIOD_COST&quot; Double precision,&quot;TRAN_OUT_PERIOD&quot; Integer,&quot;TRAN_OUT_PERIOD_COST&quot; Double precision,&quot;UNITS_SOLD_PERIOD&quot; Integer,&quot;REFUNDS_PERIOD&quot; Integer,&quot;TOTAL_UNITS_SOLD_PERIOD&quot; Integer,&quot;COST_SOLD_PERIOD&quot; Double precision,&quot;SALES_PERIOD&quot; Double precision,&quot;LAYAWAY_PERIOD&quot; Integer,&quot;LAYAWAY_PERIOD_COST&quot; Integer,&quot;TOTAL_UNITS_SOLD_OUT_OF_PERIOD&quot; Integer,&quot;INVENTARIO_INICIAL&quot; Double precision,&quot;INVENTARIO_FINAL&quot; Double precision,&quot;COST_PERIOD&quot; Double precision,&quot;RCVD_BEFORE&quot; Integer,&quot;RCVD_BEFORE_COST&quot; Double precision,&quot;UNITS_SOLD_BEFORE&quot; Integer,&quot;SOLD_BEFORE_COST&quot; Double precision,&quot;INTRANSIT_PERIOD&quot; Integer,&quot;COST_INTRANSIT_PERIOD&quot; Double precision,&quot;COST_INITIAL_PERIOD&quot; Double precision,&quot;COST_END_PERIOD&quot; Double precision,&quot;DTE_START&quot; Date,&quot;DTE_END&quot; Date,&quot;AVG_COST&quot; Double precision)<br> AS<br> <br> <br> BEGIN<br> <br> <br>        FOR SELECT style,brand,dept,typ,season,ext,price_pt,gender,subtype,store FROM SKU_PROCEDURE ORDER BY BRAND,STYLE INTO :style,:brand,:dept,:typ,:season,:ext,:price_pt,:gender,:subtype,:store<br>        DO<br>           BEGIN<br>           EXECUTE PROCEDURE GET_INVT_DETAIL_PERIOD (:store,:dte1,:dte2,:brand,:style,:ext)<br>           returning_values (:RCVD_PERIOD,:RCVD_PERIOD_COST,:TRAN_IN_PERIOD,:TRAN_IN_PERIOD_COST,:BO_PERIOD,BO_PERIOD_COST,:TRAN_OUT_PERIOD,:TRAN_OUT_PERIOD_COST,:UNITS_SOLD_PERIOD,<br>           :REFUNDS_PERIOD,:TOTAL_UNITS_SOLD_PERIOD,:COST_SOLD_PERIOD,:SALES_PERIOD,:LAYAWAY_PERIOD,:LAYAWAY_PERIOD_COST,<br>           :INVENTARIO_INICIAL,:INVENTARIO_FINAL,:COST_PERIOD,:RCVD_BEFORE,:RCVD_BEFORE_COST,<br>           :UNITS_SOLD_BEFORE,:SOLD_BEFORE_COST,:INTRANSIT_PERIOD,:COST_INTRANSIT_PERIOD,:COST_INITIAL_PERIOD,:COST_END_PERIOD,:DTE_START,:DTE_END,:AVG_COST);<br>           SUSPEND;<br>           END<br> <br> <br> <br> <br> <br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_5"></a>
<div class="caption1">MK_UPDATE</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;MK_UPDATE&quot; (&quot;DTE1&quot; Date,&quot;DTE2&quot; Date) RETURNS (&quot;STYLE&quot; VarChar(30),&quot;BRAND&quot; VarChar(30))<br> AS<br> <br> BEGIN<br> FOR select STYLE,BRAND from mkdtl where low_dte&gt;=:DTE1 and high_dte&lt;=:DTE2 INTO :STYLE,BRAND<br>   DO<br>     BEGIN<br>     update sku set stat_mrk='T' WHERE STYLE=:STYLE AND BRAND=:BRAND;<br> <br>     END<br> <br> FOR select STYLE,BRAND from mkdtl where FROM_MODE=0 INTO :STYLE,BRAND<br>   DO<br>     BEGIN<br>     update sku set stat_mrk='T' WHERE STYLE=:STYLE AND BRAND=:BRAND;<br> <br>     END<br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_3"></a>
<div class="caption1">PEDIDO_MAX_MIN</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;PEDIDO_MAX_MIN&quot;  RETURNS (&quot;STORE&quot; Integer,&quot;BRAND&quot; VarChar(30),&quot;STYLE&quot; VarChar(30),&quot;EXT&quot; VarChar(8),&quot;SIZ&quot; VarChar(10),&quot;PRE1&quot; Integer,&quot;PRE2&quot; Integer,&quot;PRE3&quot; Integer,&quot;PRE4&quot; Integer,&quot;PRE5&quot; Integer,&quot;PRE6&quot; Integer,&quot;PRE7&quot; Integer,&quot;PRE8&quot; Integer,&quot;PRE9&quot; Integer,&quot;PRE10&quot; Integer,&quot;PRE11&quot; Integer,&quot;PRE12&quot; Integer,&quot;PRE13&quot; Integer,&quot;PRE14&quot; Integer,&quot;PRE15&quot; Integer,&quot;PRE16&quot; Integer,&quot;PRE17&quot; Integer,&quot;PRE18&quot; Integer,&quot;PRE19&quot; Integer,&quot;PRE20&quot; Integer,&quot;PRE21&quot; Integer,&quot;PRE22&quot; Integer)<br> AS<br> <br> DECLARE VARIABLE S1	VARCHAR(8);<br> DECLARE VARIABLE S2	VARCHAR(8);<br> DECLARE VARIABLE S3	VARCHAR(8);<br> DECLARE VARIABLE S4	VARCHAR(8);<br> DECLARE VARIABLE S5	VARCHAR(8);<br> DECLARE VARIABLE S6	VARCHAR(8);<br> DECLARE VARIABLE S7	VARCHAR(8);<br> DECLARE VARIABLE S8	VARCHAR(8);<br> DECLARE VARIABLE S9	VARCHAR(8);<br> DECLARE VARIABLE S10	VARCHAR(8);<br> DECLARE VARIABLE S11	VARCHAR(8);<br> DECLARE VARIABLE S12	VARCHAR(8);<br> DECLARE VARIABLE S13	VARCHAR(8);<br> DECLARE VARIABLE S14	VARCHAR(8);<br> DECLARE VARIABLE S15	VARCHAR(8);<br> DECLARE VARIABLE S16	VARCHAR(8);<br> DECLARE VARIABLE S17	VARCHAR(8);<br> DECLARE VARIABLE S18	VARCHAR(8);<br> DECLARE VARIABLE S19	VARCHAR(8);<br> DECLARE VARIABLE S20	VARCHAR(8);<br> DECLARE VARIABLE S21	VARCHAR(8);<br> DECLARE VARIABLE S22	VARCHAR(8);<br> <br> DECLARE VARIABLE  PO_NUM	INTEGER ;<br> DECLARE VARIABLE  PO_STORE	INTEGER;<br> <br> DECLARE VARIABLE  SIZESCALE	INTEGER;<br> DECLARE VARIABLE  PEDIR	INTEGER;<br> <br> DECLARE VARIABLE  PREV_STORE	INTEGER;<br> DECLARE VARIABLE  PREV_BRAND	VARCHAR(30);<br> DECLARE VARIABLE  PREV_STYLE	VARCHAR(30);<br> DECLARE VARIABLE  PREV_EXT	VARCHAR(8);<br> DECLARE VARIABLE  PREV_SIZ      VARCHAR (10);<br> <br> DECLARE VARIABLE  TEMP_STORE	INTEGER;<br> DECLARE VARIABLE  TEMP_BRAND	VARCHAR(30);<br> DECLARE VARIABLE  TEMP_STYLE	VARCHAR(30);<br> DECLARE VARIABLE  TEMP_EXT	VARCHAR(8);<br> <br> DECLARE VARIABLE  TRQ1	INTEGER;<br> DECLARE VARIABLE  TRQ2	INTEGER;<br> DECLARE VARIABLE  TRQ3	INTEGER;<br> DECLARE VARIABLE  TRQ4	INTEGER;<br> DECLARE VARIABLE  TRQ5	INTEGER;<br> DECLARE VARIABLE  TRQ6	INTEGER;<br> DECLARE VARIABLE  TRQ7	INTEGER;<br> DECLARE VARIABLE  TRQ8	INTEGER;<br> DECLARE VARIABLE  TRQ9	INTEGER;<br> DECLARE VARIABLE  TRQ10	INTEGER;<br> DECLARE VARIABLE  TRQ11	INTEGER;<br> DECLARE VARIABLE  TRQ12	INTEGER;<br> DECLARE VARIABLE  TRQ13	INTEGER;<br> DECLARE VARIABLE  TRQ14	INTEGER;<br> DECLARE VARIABLE  TRQ15	INTEGER;<br> DECLARE VARIABLE  TRQ16	INTEGER;<br> DECLARE VARIABLE  TRQ17	INTEGER;<br> DECLARE VARIABLE  TRQ18	INTEGER;<br> DECLARE VARIABLE  TRQ19	INTEGER;<br> DECLARE VARIABLE  TRQ20	INTEGER;<br> DECLARE VARIABLE  TRQ21	INTEGER;<br> DECLARE VARIABLE  TRQ22	INTEGER;<br> <br> BEGIN<br> 	PRE1	= 0;<br> 	PRE2	= 0;<br> 	PRE3	= 0;<br> 	PRE4	= 0;<br> 	PRE5	= 0;<br> 	PRE6	= 0;<br> 	PRE7	= 0;<br> 	PRE8	= 0;<br> 	PRE9	= 0;<br> 	PRE10	= 0;<br> 	PRE11	= 0;<br> 	PRE12	= 0;<br> 	PRE13	= 0;<br> 	PRE14	= 0;<br> 	PRE15	= 0;<br> 	PRE16	= 0;<br> 	PRE17	= 0;<br> 	PRE18	= 0;<br> 	PRE19	= 0;<br> 	PRE20	= 0;<br> 	PRE21	= 0;<br> 	PRE22	= 0;<br> <br> 	TRQ1	= 0;<br> 	TRQ2	= 0;<br> 	TRQ3	= 0;<br> 	TRQ4	= 0;<br> 	TRQ5	= 0;<br> 	TRQ6	= 0;<br> 	TRQ7	= 0;<br> 	TRQ8	= 0;<br> 	TRQ9	= 0;<br> 	TRQ10	= 0;<br> 	TRQ11	= 0;<br> 	TRQ12	= 0;<br> 	TRQ13	= 0;<br> 	TRQ14	= 0;<br> 	TRQ15	= 0;<br> 	TRQ16	= 0;<br> 	TRQ17	= 0;<br> 	TRQ18	= 0;<br> 	TRQ19	= 0;<br> 	TRQ20	= 0;<br> 	TRQ21	= 0;<br> 	TRQ22	= 0;<br>         PREV_STORE = -1;<br>   for   select i.store,<br>       i.brand,<br>       i.style,<br>       i.ext,<br>       i.siz,<br>       ((MINI/65536) - quan) - (ord + INTRANSIT)  as pedir,<br>       s.sizescale<br>      from  invt i inner join sku s on i.brand = s.brand and i.style = s.style<br>                   inner join sku_resurtido r on i.store = r.store and i.brand = r.brand and i.style = r.style and i.ext = r.ext<br>      Where  mini &lt;&gt; 0 AND (Mini - ( 65536 *(MINI/65536))) &gt; (QUAN + ORD + INTRANSIT)<br>      order by i.store, i.brand,i.style, i.ext, i.siz<br>   into :STORE, :BRAND, :STYLE, :EXT, :SIZ,PEDIR, :SIZESCALE do<br>   begin<br> <br>     IF ( (STORE &lt;&gt; PREV_STORE OR BRAND &lt;&gt; PREV_BRAND OR STYLE &lt;&gt; PREV_STYLE OR EXT &lt;&gt; PREV_EXT) AND PREV_STORE &lt;&gt; -1) THEN<br>     BEGIN<br>       SELECT sum(O1) as P1,sum(O2) as P2,sum(O3) as P3,sum(O4) as P4,sum(O5) as P5,sum(O6) as P6,sum(O7) as P7,sum(O8) as P8,sum(O9) as P9,sum(O10) as P10,<br>              sum(O11) as P11,sum(O12) as P12,sum(O13) as P13,sum(O14) as P14,sum(O15) as P15,sum(O16) as P16,sum(O17) as P17,sum(O18) as P18,sum(O19) as P19,sum(O20) as P20,<br>              sum(O21) as P21,sum(O22) as P22<br>       FROM TRQLINE l INNER JOIN TRQMAIN M ON l.TRQ_NUM = m.NUM AND l.REQSTORE = m.REQSTORE AND m.CLOSED = 0<br>       WHERE REQSTORE = :PREV_STORE AND BRAND = :PREV_BRAND AND STYLE = :PREV_STYLE AND EXT = :PREV_EXT<br>       INTO TRQ1,TRQ2,TRQ3,TRQ4,TRQ5,TRQ6,TRQ7,TRQ8,TRQ9,TRQ10,TRQ11,TRQ12,TRQ13,TRQ14,TRQ15,TRQ16,TRQ17,TRQ18,TRQ19,TRQ20,TRQ21,TRQ22;<br> <br>       IF (TRQ1 IS NULL ) THEN<br>           TRQ1 = 0;<br>       IF (TRQ2 IS NULL ) THEN<br>           TRQ2 = 0;<br>       IF (TRQ3 IS NULL ) THEN<br>           TRQ3 = 0;<br>       IF (TRQ4 IS NULL ) THEN<br>           TRQ4 = 0;<br>       IF (TRQ5 IS NULL ) THEN<br>           TRQ5 = 0;<br>       IF (TRQ6 IS NULL ) THEN<br>           TRQ6 = 0;<br>       IF (TRQ7 IS NULL ) THEN<br>           TRQ7 = 0;<br>       IF (TRQ8 IS NULL ) THEN<br>           TRQ8 = 0;<br>       IF (TRQ9 IS NULL ) THEN<br>           TRQ9 = 0;<br>       IF (TRQ10 IS NULL ) THEN<br>           TRQ10 = 0;<br> <br>       IF (TRQ11 IS NULL ) THEN<br>           TRQ11 = 0;<br>       IF (TRQ12 IS NULL ) THEN<br>           TRQ12 = 0;<br>       IF (TRQ13 IS NULL ) THEN<br>           TRQ13 = 0;<br>       IF (TRQ14 IS NULL ) THEN<br>           TRQ14 = 0;<br>       IF (TRQ15 IS NULL ) THEN<br>           TRQ15 = 0;<br>       IF (TRQ16 IS NULL ) THEN<br>           TRQ16 = 0;<br>       IF (TRQ17 IS NULL ) THEN<br>           TRQ17 = 0;<br>       IF (TRQ18 IS NULL ) THEN<br>           TRQ18 = 0;<br>       IF (TRQ19 IS NULL ) THEN<br>           TRQ19 = 0;<br>       IF (TRQ20 IS NULL ) THEN<br>           TRQ20 = 0;<br> <br>       IF (TRQ21 IS NULL ) THEN<br>           TRQ21 = 0;<br>       IF (TRQ22 IS NULL ) THEN<br>           TRQ22 = 0;<br> <br>       PRE1 = PRE1 - TRQ1;<br>       PRE2 = PRE2 - TRQ2;<br>       PRE3 = PRE3 - TRQ3;<br>       PRE4 = PRE4 - TRQ4;<br>       PRE5 = PRE5 - TRQ5;<br>       PRE6 = PRE6 - TRQ6;<br>       PRE7 = PRE7 - TRQ7;<br>       PRE8 = PRE8 - TRQ8;<br>       PRE9 = PRE9 - TRQ9;<br>       PRE10 = PRE10 - TRQ10;<br>       PRE11 = PRE11 - TRQ11;<br>       PRE12 = PRE12 - TRQ12;<br>       PRE13 = PRE13 - TRQ13;<br>       PRE14 = PRE14 - TRQ14;<br>       PRE15 = PRE15 - TRQ15;<br>       PRE16 = PRE16 - TRQ16;<br>       PRE17 = PRE17 - TRQ17;<br>       PRE18 = PRE18 - TRQ18;<br>       PRE19 = PRE19 - TRQ19;<br>       PRE20 = PRE20 - TRQ20;<br>       PRE21 = PRE21 - TRQ21;<br>       PRE22 = PRE22 - TRQ22;<br> <br>       IF (PRE1 &lt; 0) THEN<br>           PRE1 = 0;<br>       IF (PRE2 &lt; 0) THEN<br>           PRE2 = 0;<br>       IF (PRE3 &lt; 0) THEN<br>           PRE3 = 0;<br>       IF (PRE4 &lt; 0) THEN<br>           PRE4 = 0;<br>       IF (PRE5 &lt; 0) THEN<br>           PRE5 = 0;<br>       IF (PRE6 &lt; 0) THEN<br>           PRE6 = 0;<br>       IF (PRE7 &lt; 0) THEN<br>           PRE7 = 0;<br>       IF (PRE8 &lt; 0) THEN<br>           PRE8 = 0;<br>       IF (PRE9 &lt; 0) THEN<br>           PRE9 = 0;<br>       IF (PRE10 &lt; 0) THEN<br>           PRE10 = 0;<br>       IF (PRE11 &lt; 0) THEN<br>           PRE11 = 0;<br>       IF (PRE12 &lt; 0) THEN<br>           PRE12 = 0;<br>       IF (PRE13 &lt; 0) THEN<br>           PRE13 = 0;<br>       IF (PRE14 &lt; 0) THEN<br>           PRE14 = 0;<br>       IF (PRE15 &lt; 0) THEN<br>           PRE15 = 0;<br>       IF (PRE16 &lt; 0) THEN<br>           PRE16 = 0;<br>       IF (PRE17 &lt; 0) THEN<br>           PRE17 = 0;<br>       IF (PRE18 &lt; 0) THEN<br>           PRE18 = 0;<br>       IF (PRE19 &lt; 0) THEN<br>           PRE19 = 0;<br>       IF (PRE20 &lt; 0) THEN<br>           PRE20 = 0;<br>       IF (PRE21 &lt; 0) THEN<br>           PRE21 = 0;<br>       IF (PRE22 &lt; 0) THEN<br>           PRE22 = 0;<br>     TEMP_STORE = STORE;<br>     TEMP_STYLE = STYLE;<br>     TEMP_BRAND = BRAND;<br>     TEMP_EXT   = EXT;<br> <br>     STORE = PREV_STORE;<br>     STYLE = PREV_STYLE;<br>     BRAND = PREV_BRAND;<br>     EXT   = PREV_EXT;<br>       SUSPEND;<br>     STORE = TEMP_STORE;<br>     STYLE = TEMP_STYLE;<br>     BRAND = TEMP_BRAND;<br>     EXT   = TEMP_EXT;<br> <br>         PRE1	= 0;<br> 	PRE2	= 0;<br> 	PRE3	= 0;<br> 	PRE4	= 0;<br> 	PRE5	= 0;<br> 	PRE6	= 0;<br> 	PRE7	= 0;<br> 	PRE8	= 0;<br> 	PRE9	= 0;<br> 	PRE10	= 0;<br> 	PRE11	= 0;<br> 	PRE12	= 0;<br> 	PRE13	= 0;<br> 	PRE14	= 0;<br> 	PRE15	= 0;<br> 	PRE16	= 0;<br> 	PRE17	= 0;<br> 	PRE18	= 0;<br> 	PRE19	= 0;<br> 	PRE20	= 0;<br> 	PRE21	= 0;<br> 	PRE22	= 0;<br>         SELECT S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14,S15,S16,S17,S18,S19,S20,S21,S22<br>         FROM SIZES WHERE SCALE = :SIZESCALE<br>         INTO :S1,:S2,:S3,:S4,:S5,:S6,:S7,:S8,:S9,:S10,:S11,:S12,:S13,:S14,:S15,:S16,:S17,:S18,:S19,:S20,:S21,:S22;<br>     END<br>     ELSE<br>       BEGIN<br>        IF (PREV_STORE = -1) THEN<br>           SELECT S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14,S15,S16,S17,S18,S19,S20,S21,S22<br>           FROM SIZES WHERE SCALE = :SIZESCALE<br>           INTO :S1,:S2,:S3,:S4,:S5,:S6,:S7,:S8,:S9,:S10,:S11,:S12,:S13,:S14,:S15,:S16,:S17,:S18,:S19,:S20,:S21,:S22;<br>       END<br> <br>     IF (SIZ = S1) THEN<br>     BEGIN<br>        PRE1 = PEDIR;<br>     END<br>     IF (SIZ = S2) THEN<br>     BEGIN<br>        PRE2 = PEDIR;<br>     END<br>     IF (SIZ = S3) THEN<br>     BEGIN<br>        PRE3 = PEDIR;<br>     END<br>     IF (SIZ = S4) THEN<br>     BEGIN<br>        PRE4 = PEDIR;<br>     END<br>     IF (SIZ = S5) THEN<br>     BEGIN<br>        PRE5 = PEDIR;<br>     END<br>     IF (SIZ = S6) THEN<br>     BEGIN<br>        PRE6 = PEDIR;<br>     END<br>     IF (SIZ = S7) THEN<br>     BEGIN<br>        PRE7 = PEDIR;<br>     END<br>     IF (SIZ = S8) THEN<br>     BEGIN<br>        PRE8 = PEDIR;<br>     END<br>     IF (SIZ = S9) THEN<br>     BEGIN<br>        PRE9 = PEDIR;<br>     END<br>     IF (SIZ = S10) THEN<br>     BEGIN<br>        PRE10 = PEDIR;<br>     END<br>     IF (SIZ = S11) THEN<br>     BEGIN<br>        PRE11 = PEDIR;<br>     END<br>     IF (SIZ = S12) THEN<br>     BEGIN<br>        PRE12 = PEDIR;<br>     END<br>     IF (SIZ = S13) THEN<br>     BEGIN<br>        PRE13 = PEDIR;<br>     END<br>     IF (SIZ = S14) THEN<br>     BEGIN<br>        PRE14 = PEDIR;<br>     END<br>     IF (SIZ = S15) THEN<br>     BEGIN<br>        PRE15 = PEDIR;<br>     END<br>     IF (SIZ = S16) THEN<br>     BEGIN<br>        PRE16 = PEDIR;<br>     END<br>     IF (SIZ = S17) THEN<br>     BEGIN<br>        PRE17 = PEDIR;<br>     END<br>     IF (SIZ = S18) THEN<br>     BEGIN<br>        PRE18 = PEDIR;<br>     END<br>     IF (SIZ = S19) THEN<br>     BEGIN<br>        PRE19 = PEDIR;<br>     END<br>     IF (SIZ = S20) THEN<br>     BEGIN<br>        PRE20 = PEDIR;<br>     END<br>     IF (SIZ = S21) THEN<br>     BEGIN<br>        PRE21 = PEDIR;<br>     END<br>     IF (SIZ = S22) THEN<br>     BEGIN<br>        PRE22 = PEDIR;<br>     END<br> <br>     PREV_STORE = STORE;<br>     PREV_STYLE = STYLE;<br>     PREV_BRAND = BRAND;<br>     PREV_EXT   = EXT;<br>   END<br>   SUSPEND;<br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_19"></a>
<div class="caption1">SEASON_UPDATE</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;SEASON_UPDATE&quot; (&quot;DTE1&quot; Date,&quot;DTE2&quot; Date) RETURNS (&quot;STYLE&quot; VarChar(30),&quot;BRAND&quot; VarChar(30),&quot;DTE&quot; Date,&quot;PO_NUM&quot; Integer,&quot;MONTH_RCVD&quot; Integer,&quot;YEAR_RCVD&quot; Integer,&quot;SEASON&quot; VarChar(30))<br> AS<br> <br> DECLARE VARIABLE SEASON1 VARCHAR(15);<br> <br> BEGIN<br> <br> FOR SELECT STYLE,BRAND,DTE,PO_NUM,extract(year from dte),extract(month from dte)<br>     FROM TRANSFER WHERE DTE BETWEEN :DTE1 AND :DTE2 AND TRAN_NUM IS NULL<br>     GROUP BY STYLE,BRAND,DTE,PO_NUM,EXT INTO :STYLE,:BRAND,:DTE,:PO_NUM,:YEAR_RCVD,:MONTH_RCVD<br>     DO<br>     BEGIN<br>       IF (:MONTH_RCVD=1 OR :MONTH_RCVD=2 OR :MONTH_RCVD=3) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q1';<br>       IF (:MONTH_RCVD=4 OR :MONTH_RCVD=5 OR :MONTH_RCVD=6) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q2';<br>       IF (:MONTH_RCVD=7 OR :MONTH_RCVD=8 OR :MONTH_RCVD=9) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q3';<br>       IF (:MONTH_RCVD=10 OR :MONTH_RCVD=11 OR :MONTH_RCVD=12) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q4';<br> <br>         SELECT SEASON FROM SEASONS WHERE SEASON= :SEASON INTO :SEASON1;<br>         IF (:SEASON1 IS NULL) THEN<br>          BEGIN<br>            INSERT INTO SEASONS (SEASON,DLU) VALUES (:SEASON,'NOW');<br>          END<br> <br>         UPDATE SKU SET SEASON=:SEASON WHERE STYLE=:STYLE AND BRAND=:BRAND;<br> <br> <br> <br> <br>     END<br> <br> <br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_2"></a>
<div class="caption1">SELECT_RECEIPT</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;SELECT_RECEIPT&quot;  RETURNS (&quot;NUM&quot; Integer,&quot;STORE&quot; Integer,&quot;CUST_NUM&quot; Integer,&quot;SUB_TOTAL&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2),&quot;AFT_DISC&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;OFF_FRM&quot; Integer,&quot;DISC_FRM&quot; Integer)<br> AS<br> <br> DECLARE VARIABLE FLAG INTEGER;<br> <br> <br> BEGIN<br> <br> FOR SELECT NUM,STORE,CUST_NUM,SUB_TOTAL,TOTAL_DISC,AFT_DISC,TOTAL,FREE_DOL_USED<br> FROM RECEIPT<br> WHERE FREE_DOL_USED&lt;&gt;0 AND AFT_DISC&lt;&gt;SUB_TOTAL -FREE_DOL_USED<br> INTO :NUM,STORE,CUST_NUM,SUB_TOTAL,TOTAL_DISC,AFT_DISC,TOTAL,FREE_DOL_USED<br> <br> DO<br> BEGIN<br> FLAG=0;<br> FOR SELECT OFF_FRM FROM RECLINE WHERE REC_NUM=:NUM AND STORE=:STORE GROUP BY OFF_FRM INTO :OFF_FRM<br>   DO<br>   BEGIN<br>    IF (:OFF_FRM&lt;&gt;0) THEN<br>     BEGIN<br>      FLAG=1;<br>     END<br>   END<br>   DISC_FRM=:FLAG;<br>   SUSPEND;<br> <br> END<br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_20"></a>
<div class="caption1">SELECT_SEASON</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;SELECT_SEASON&quot; (&quot;DTE1&quot; Date,&quot;DTE2&quot; Date) RETURNS (&quot;STYLE&quot; VarChar(30),&quot;EXT&quot; VarChar(8),&quot;BRAND&quot; VarChar(30),&quot;DTE&quot; Date,&quot;PO_NUM&quot; Integer,&quot;MONTH_RCVD&quot; Integer,&quot;YEAR_RCVD&quot; Integer,&quot;SEASON&quot; VarChar(30))<br> AS<br> <br> BEGIN<br> <br> FOR SELECT STYLE,BRAND,EXT,DTE,PO_NUM,extract(year from dte),extract(month from dte)<br>     FROM TRANSFER WHERE DTE BETWEEN :DTE1 AND :DTE2 AND TRAN_NUM IS NULL<br>     GROUP BY STYLE,BRAND,EXT,DTE,PO_NUM INTO :STYLE,:BRAND,:EXT,:DTE,:PO_NUM,:YEAR_RCVD,:MONTH_RCVD<br>     DO<br>     BEGIN<br>       IF (:MONTH_RCVD=1 OR :MONTH_RCVD=2 OR :MONTH_RCVD=3) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q1';<br>       IF (:MONTH_RCVD=4 OR :MONTH_RCVD=5 OR :MONTH_RCVD=6) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q2';<br>       IF (:MONTH_RCVD=7 OR :MONTH_RCVD=8 OR :MONTH_RCVD=9) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q3';<br>       IF (:MONTH_RCVD=10 OR :MONTH_RCVD=11 OR :MONTH_RCVD=12) THEN<br>        SEASON=CAST(:YEAR_RCVD AS VARCHAR(4)) ||' Q4';<br>       SUSPEND;<br>     END<br> <br> <br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_6"></a>
<div class="caption1">SHOW_BUY_CLUB_ALL</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;SHOW_BUY_CLUB_ALL&quot;  RETURNS (&quot;CUST_NUM&quot; Integer,&quot;CUST_STORE&quot; Integer,&quot;ID&quot; VarChar(40),&quot;NAME&quot; VarChar(50),&quot;PHONE&quot; VarChar(15),&quot;FREE_DOL&quot; Decimal(15,2),&quot;FREE_PURCH&quot; Decimal(15,2),&quot;PAID&quot; Decimal(15,2),&quot;AFT_DISC&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;FREE_DOL_CALC&quot; Decimal(15,2),&quot;FREE_PURCH_CALC&quot; Decimal(15,2),&quot;FREE_DOL_OK&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2))<br> AS<br> <br> DECLARE VARIABLE DISC DECIMAL(15,2);<br> DECLARE VARIABLE DTE1 DATE ;<br> DECLARE VARIABLE DTE2 DATE;<br> DECLARE VARIABLE  STORE INTEGER;<br> DECLARE VARIABLE  DTE  DATE;<br> DECLARE VARIABLE  PERC DECIMAL(15,2);<br> DECLARE VARIABLE  CALC DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_REG DECIMAL(15,2);<br> DECLARE VARIABLE AFT_DISC_REG DECIMAL(15,2);<br> DECLARE VARIABLE PAID_REG DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_USED DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_PURCH DECIMAL(15,2);<br> <br> <br> BEGIN<br> <br> FOR SELECT CUST_NUM,STORE,ID,FIRST || ' ' || LAST, FREE_DOL,FREE_PURCH,PHONE from CUST<br>  where buying_club='T' AND FREE_PURCH&lt;&gt;0<br>  INTO :CUST_NUM,:CUST_STORE,:ID,:NAME,:FREE_DOL,:FREE_PURCH,:PHONE<br>  do<br> <br>  BEGIN<br>   FREE_DOL_CALC=0;<br>   TOTAL=0;<br>   TOTAL_FREE_PURCH=0;<br>   FREE_PURCH_CALC=0;<br>   TOTAL_FREE_USED=0;<br>   FREE_DOL_USED=0;<br>   AFT_DISC=0;<br>   FOR SELECT TOTAL,PAID,AFT_DISC,FREE_DOL_USED,DTE,STORE,TOTAL_DISC FROM RECEIPT WHERE CUST_NUM=:CUST_NUM AND CUST_STORE=:CUST_STORE<br>   INTO :TOTAL_REG,:PAID_REG,:AFT_DISC_REG,:TOTAL_FREE_USED,:DTE,:STORE,:TOTAL_DISC<br> <br>   DO<br> <br>   BEGIN<br> <br>         SELECT DISC FROM  DISC_BUYIN_CLUB WHERE STORE=:STORE AND DTE1&lt; :DTE AND DTE2&gt; :DTE<br>         INTO :DISC;<br>         IF (:DISC  IS NOT NULL AND :DISC&gt; 0 ) THEN<br>            BEGIN<br>             CALC=:AFT_DISC_REG * (:DISC/100);<br>             TOTAL_FREE_PURCH=:AFT_DISC_REG;<br>             IF (TOTAL_FREE_USED=0) THEN<br>               BEGIN<br>                 TOTAL_FREE_USED=:TOTAL_DISC;<br>               END<br> <br>            END<br> <br>         ELSE<br>            BEGIN<br> <br>            CALC=0;<br>            TOTAL_FREE_PURCH=0;<br> <br> <br>            END<br>    FREE_DOL_CALC=: FREE_DOL_CALC+ :CALC;<br>    TOTAL=:TOTAL +:TOTAL_REG;<br>    AFT_DISC=:AFT_DISC+:AFT_DISC_REG;<br>    FREE_PURCH_CALC=:FREE_PURCH_CALC+:TOTAL_FREE_PURCH;<br>    FREE_DOL_USED=:FREE_DOL_USED + :TOTAL_FREE_USED;<br>    FREE_DOL_OK=:FREE_DOL_CALC-:FREE_DOL_USED;<br>    END<br> <br> <br> SUSPEND;<br> END<br> <br> <br> end<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_8"></a>
<div class="caption1">SHOW_BUY_CLUB_CUST</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;SHOW_BUY_CLUB_CUST&quot; (&quot;CUST_NUM1&quot; Integer) RETURNS (&quot;CUST_NUM&quot; Integer,&quot;CUST_STORE&quot; Integer,&quot;ID&quot; VarChar(40),&quot;NAME&quot; VarChar(50),&quot;FREE_DOL&quot; Decimal(15,2),&quot;FREE_PURCH&quot; Decimal(15,2),&quot;PAID&quot; Decimal(15,2),&quot;AFT_DISC&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;FREE_DOL_CALC&quot; Decimal(15,2),&quot;FREE_PURCH_CALC&quot; Decimal(15,2),&quot;FREE_DOL_OK&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2))<br> AS<br> <br> DECLARE VARIABLE DISC DECIMAL(15,2);<br> DECLARE VARIABLE DTE1 DATE ;<br> DECLARE VARIABLE DTE2 DATE;<br> DECLARE VARIABLE STORE INTEGER;<br> DECLARE VARIABLE DTE  DATE;<br> DECLARE VARIABLE PERC DECIMAL(15,2);<br> DECLARE VARIABLE CALC DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_REG DECIMAL(15,2);<br> DECLARE VARIABLE AFT_DISC_REG DECIMAL(15,2);<br> DECLARE VARIABLE PAID_REG DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_USED DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_PURCH DECIMAL(15,2);<br> DECLARE VARIABLE TAX DECIMAL(15,2);<br> <br> BEGIN<br> <br> FOR SELECT CUST_NUM,STORE,ID,FIRST || ' ' || LAST ,FREE_DOL,FREE_PURCH from CUST<br>  where buying_club='T' AND CUST_NUM=:CUST_NUM1<br>  INTO :CUST_NUM,:CUST_STORE,:ID,:NAME,:FREE_DOL,:FREE_PURCH<br>  do<br> <br>  BEGIN<br>   FREE_DOL_CALC=0;<br>   TOTAL=0;<br>   AFT_DISC=0;<br>   PAID=0;<br>   TOTAL_FREE_PURCH=0;<br>   FREE_PURCH_CALC=0;<br>   TOTAL_FREE_USED=0;<br>   FREE_DOL_USED=0;<br>   FOR SELECT TOTAL,PAID,AFT_DISC,FREE_DOL_USED,DTE,STORE,TOTAL_DISC FROM RECEIPT WHERE CUST_NUM=:CUST_NUM AND CUST_STORE=:CUST_STORE<br>   INTO :TOTAL_REG,:PAID_REG,:AFT_DISC_REG,:TOTAL_FREE_USED,:DTE,:STORE,:TOTAL_DISC<br> <br>   DO<br> <br>   BEGIN<br> <br>         SELECT DISC FROM  DISC_BUYIN_CLUB WHERE STORE=:STORE AND DTE1&lt; :DTE AND DTE2&gt; :DTE<br>         INTO :DISC;<br>         IF (:DISC  IS NOT NULL AND :DISC&gt; 0 ) THEN<br>            BEGIN<br>             CALC=:AFT_DISC_REG * (:DISC/100);<br>             TOTAL_FREE_PURCH=:AFT_DISC_REG;<br> <br>             IF (TOTAL_FREE_USED=0) THEN<br>               BEGIN<br>                 TOTAL_FREE_USED=:TOTAL_DISC;<br>               END<br> <br>            END<br> <br> <br> <br> <br>         ELSE<br>            BEGIN<br> <br>            CALC=0;<br>            TOTAL_FREE_PURCH=0;<br> <br> <br>            END<br> <br>   /* SELECT STANDARD_TAX FROM STORES WHERE STORE=:CUST_STORE INTO :TAX; */<br>    FREE_DOL_CALC=: FREE_DOL_CALC+ :CALC;<br>    TOTAL=:TOTAL +:TOTAL_REG;<br>    AFT_DISC=:AFT_DISC+:AFT_DISC_REG;<br>    PAID=:PAID + :PAID_REG;<br> <br> <br>    FREE_PURCH_CALC=:FREE_PURCH_CALC+:TOTAL_FREE_PURCH;<br>    FREE_DOL_USED=:FREE_DOL_USED + :TOTAL_FREE_USED;<br>    FREE_DOL_OK=(:FREE_DOL_CALC-:FREE_DOL_USED);<br> <br> <br> <br> <br> <br>    /* FREE_DOL_OK=(:FREE_DOL_CALC-:FREE_DOL_USED)* (1+(TAX/100)); */<br>    END<br> <br> <br> SUSPEND;<br> END<br> <br> <br> end<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_9"></a>
<div class="caption1">SHOW_BUY_CLUB_DETAIL_ALL</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;SHOW_BUY_CLUB_DETAIL_ALL&quot;  RETURNS (&quot;CUST_NUM&quot; Integer,&quot;CUST_STORE&quot; Integer,&quot;ID&quot; VarChar(40),&quot;NAME&quot; VarChar(50),&quot;FREE_DOL&quot; Decimal(15,2),&quot;FREE_PURCH&quot; Decimal(15,2),&quot;SUB_TOTAL&quot; Decimal(15,2),&quot;AFT_DISC&quot; Decimal(15,2),&quot;PAID&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;FREE_DOL_CALC&quot; Decimal(15,2),&quot;STORE&quot; Integer,&quot;DTE&quot; Date,&quot;PERC&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2))<br> AS<br> <br> DECLARE VARIABLE DISC DECIMAL(15,2);<br> DECLARE VARIABLE DTE1 DATE ;<br> DECLARE VARIABLE DTE2 DATE;<br> <br> BEGIN<br> <br> FOR SELECT CUST_NUM,STORE,ID,FIRST || ' ' || LAST, FREE_DOL,FREE_PURCH from CUST<br>  where buying_club='T' INTO :CUST_NUM,:CUST_STORE,:ID,:NAME,:FREE_DOL,:FREE_PURCH<br>  do<br> <br>  BEGIN<br> <br>   FOR SELECT SUB_TOTAL,TOTAL,AFT_DISC,PAID,FREE_DOL_USED,DTE,STORE,TOTAL_DISC FROM RECEIPT WHERE CUST_NUM=:CUST_NUM AND CUST_STORE=:CUST_STORE<br>   INTO :SUB_TOTAL,:TOTAL,AFT_DISC,:PAID,:FREE_DOL_USED,:DTE,:STORE,:TOTAL_DISC<br>   DO<br>   BEGIN<br>         SELECT DISC FROM  DISC_BUYIN_CLUB WHERE STORE=:STORE AND DTE1&lt; :DTE AND DTE2&gt; :DTE<br>         INTO :DISC;<br>         IF (:DISC  IS NOT NULL AND :DISC&gt; 0 ) THEN<br>            BEGIN<br>             PERC=:DISC;<br>             FREE_DOL_CALC=:AFT_DISC * (:DISC /100);<br> <br>             IF (FREE_DOL_USED=0) THEN<br>               BEGIN<br>                 FREE_DOL_USED=:TOTAL_DISC;<br>               END<br> <br>             SUSPEND;<br> <br>            END<br> <br>         ELSE<br>            BEGIN<br>            PERC=:DISC;<br>            FREE_DOL_CALC=0;<br>            SUSPEND;<br> <br>            END<br> <br> <br>    END<br> <br> <br> END<br> <br> <br> end<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_10"></a>
<div class="caption1">SHOW_BUY_CLUB_DETAIL_CUST</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;SHOW_BUY_CLUB_DETAIL_CUST&quot; (&quot;CUST_NUM1&quot; Integer) RETURNS (&quot;CUST_NUM&quot; Integer,&quot;CUST_STORE&quot; Integer,&quot;ID&quot; VarChar(40),&quot;NAME&quot; VarChar(50),&quot;FREE_DOL&quot; Decimal(15,2),&quot;FREE_PURCH&quot; Decimal(15,2),&quot;NUM&quot; Integer,&quot;AFT_DISC&quot; Decimal(15,2),&quot;SUB_TOTAL&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;DISC_RECEIPT&quot; Decimal(15,2),&quot;PAID&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;FREE_DOL_CALC&quot; Decimal(15,2),&quot;STORE&quot; Integer,&quot;DTE&quot; Date,&quot;PERC&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2))<br> AS<br> <br> DECLARE VARIABLE DISC DECIMAL(15,2);<br> DECLARE VARIABLE DTE1 DATE ;<br> DECLARE VARIABLE DTE2 DATE;<br> <br> BEGIN<br> <br> FOR SELECT CUST_NUM,STORE,ID,FIRST || ' ' || LAST , FREE_DOL,FREE_PURCH from CUST<br>  where buying_club='T' AND CUST_NUM=:CUST_NUM1 INTO :CUST_NUM,:CUST_STORE,:ID,NAME,:FREE_DOL,:FREE_PURCH<br>  do<br> <br>  BEGIN<br> <br>   FOR SELECT NUM,AFT_DISC,LINE_DISC-FREE_DOL_USED,SUB_TOTAL,TOTAL,PAID,FREE_DOL_USED,DTE,STORE,TOTAL_DISC FROM RECEIPT WHERE CUST_NUM=:CUST_NUM<br>   INTO :NUM,:AFT_DISC,:DISC_RECEIPT,:SUB_TOTAL,:TOTAL,:PAID,:FREE_DOL_USED,:DTE,:STORE,:TOTAL_DISC<br>   DO<br>   BEGIN<br>         SELECT DISC FROM  DISC_BUYIN_CLUB WHERE STORE=:STORE AND DTE1&lt; :DTE AND DTE2&gt; :DTE<br>         INTO :DISC;<br> <br>         SELECT SUM(AMOUNT) FROM USETEND WHERE REC_NUM=:NUM AND STORE=:STORE AND<br>         TENDER IN ('Amex','Mastercard','Visa','Cash') INTO :PAID;<br> <br>         IF (:DISC  IS NOT NULL AND :DISC&gt; 0 ) THEN<br>            BEGIN<br>             PERC=:DISC;<br>             FREE_DOL_CALC=:AFT_DISC * (:DISC/100);<br>             IF (FREE_DOL_USED=0) THEN<br>               BEGIN<br>                 FREE_DOL_USED=:TOTAL_DISC;<br>               END<br> <br>             SUSPEND;<br> <br>            END<br> <br>         ELSE<br>            BEGIN<br>            PERC=:DISC;<br>            FREE_DOL_CALC=0;<br>            SUSPEND;<br> <br>            END<br> <br> <br>    END<br> <br> <br> END<br> <br> <br> end<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_16"></a>
<div class="caption1">TEMP_MARKDOWN</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;TEMP_MARKDOWN&quot; (&quot;DTE1&quot; Date,&quot;DTE2&quot; Date) RETURNS (&quot;STYLE&quot; VarChar(20),&quot;BRAND&quot; VarChar(20),&quot;EXT&quot; VarChar(20),&quot;DESCRIPTION&quot; VarChar(30),&quot;STORE&quot; Integer,&quot;DTE_LOW&quot; Date,&quot;DTE_HIGH&quot; Date,&quot;OH&quot; Integer,&quot;PRICE&quot; Numeric(15,2),&quot;PRICE_TAX&quot; Numeric(15,2),&quot;PER_OFF&quot; Numeric(15,4),&quot;COST&quot; Numeric(15,4),&quot;PRICE_MARKDOWN&quot; Numeric(15,4),&quot;PRICE_MARKDOWN_TAX&quot; Numeric(15,4),&quot;LAST_RCVD&quot; Date,&quot;ANTIQUITY&quot; Integer,&quot;STANDARD_TAX&quot; Decimal(7,4),&quot;DEPT&quot; VarChar(15),&quot;MK_NUM&quot; Integer)<br> AS<br> <br> BEGIN<br> FOR SELECT STYLE,BRAND,EXT,DESCRIPTION,STORE,SUM(QUAN),PRICE,PER_OFF,DTE_LOW,DTE_HIGH,MAX(AVG_COST),(PRICE -((PRICE*PER_OFF)/100)),STANDARD_TAX FROM RUNSALE,INVT,STORES<br>     WHERE STORES.STORE=INVT.STORE AND STORES.STORE=RUNSALE.STORE AND<br>     INVT.STYLE=RUNSALE.STYLE AND RUNSALE.BRAND=INVT.BRAND AND INVT.STORE=RUNSALE.STORE AND  DTE_LOW&gt;= :DTE1 AND DTE_HIGH &lt;= :DTE2<br>     GROUP BY STYLE,BRAND,EXT,DESCRIPTION,STORE,PRICE,PER_OFF,DTE_LOW,DTE_HIGH,STANDARD_TAX<br>     INTO STYLE,BRAND,EXT,DESCRIPTION,STORE,OH,PRICE,PER_OFF,DTE_LOW,DTE_HIGH,COST,PRICE_MARKDOWN,STANDARD_TAX<br>  DO<br>  BEGIN<br>    SELECT DEPT FROM SKU WHERE STYLE=:STYLE AND BRAND=:BRAND INTO :DEPT;<br>    SELECT MK_NUM FROM MKDTL WHERE STYLE=:STYLE AND BRAND=:BRAND AND LOW_DTE=:DTE_LOW AND HIGH_DTE=:DTE_HIGH INTO MK_NUM;<br>    PRICE_TAX= PRICE + ((PRICE *STANDARD_TAX)/100);<br>    PRICE_MARKDOWN_TAX=PRICE_MARKDOWN + ((PRICE_MARKDOWN*STANDARD_TAX)/100);<br>    SELECT MAX(DTE) FROM TRANSFER WHERE STYLE=:STYLE AND BRAND=:BRAND AND EXT=: EXT AND STORE=:STORE AND TRAN_NUM IS NULL INTO :LAST_RCVD;<br>         ANTIQUITY= (CAST('NOW' AS DATE)-:LAST_RCVD);<br>    SUSPEND;<br> <br>  END<br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_11"></a>
<div class="caption1">UPDATE_BUY_CLUB_ALL</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;UPDATE_BUY_CLUB_ALL&quot;  RETURNS (&quot;CUST_NUM&quot; Integer,&quot;CUST_STORE&quot; Integer,&quot;FREE_DOL&quot; Decimal(15,2),&quot;FREE_PURCH&quot; Decimal(15,2),&quot;PAID&quot; Decimal(15,2),&quot;AFT_DISC&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;FREE_DOL_CALC&quot; Decimal(15,2),&quot;FREE_PURCH_CALC&quot; Decimal(15,2),&quot;FREE_DOL_OK&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2))<br> AS<br> <br> DECLARE VARIABLE DISC DECIMAL(15,2);<br> DECLARE VARIABLE DTE1 DATE ;<br> DECLARE VARIABLE DTE2 DATE;<br> DECLARE VARIABLE STORE INTEGER;<br> DECLARE VARIABLE DTE  DATE;<br> DECLARE VARIABLE PERC DECIMAL(15,2);<br> DECLARE VARIABLE CALC DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_REG DECIMAL(15,2);<br> DECLARE VARIABLE AFT_DISC_REG DECIMAL(15,2);<br> DECLARE VARIABLE PAID_REG DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_USED DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_PURCH DECIMAL(15,2);<br> <br> BEGIN<br> <br> FOR SELECT CUST_NUM,STORE, FREE_DOL,FREE_PURCH from CUST<br>  where buying_club='T'<br>  INTO :CUST_NUM,:CUST_STORE,:FREE_DOL,:FREE_PURCH<br>  do<br> <br>  BEGIN<br>   FREE_DOL_CALC=0;<br>   TOTAL=0;<br>   AFT_DISC=0;<br>   PAID=0;<br>   TOTAL_FREE_PURCH=0;<br>   FREE_PURCH_CALC=0;<br>   TOTAL_FREE_USED=0;<br>   FREE_DOL_USED=0;<br>   FOR SELECT TOTAL,PAID,AFT_DISC,FREE_DOL_USED,DTE,STORE,TOTAL_DISC FROM RECEIPT WHERE CUST_NUM=:CUST_NUM AND CUST_STORE=:CUST_STORE<br>   INTO :TOTAL_REG,:PAID_REG,:AFT_DISC_REG,:TOTAL_FREE_USED,:DTE,:STORE,:TOTAL_DISC<br> <br>   DO<br> <br>   BEGIN<br> <br>         SELECT DISC FROM  DISC_BUYIN_CLUB WHERE STORE=:STORE AND DTE1&lt; :DTE AND DTE2&gt; :DTE<br>         INTO :DISC;<br>         IF (:DISC  IS NOT NULL AND :DISC&gt; 0 ) THEN<br>            BEGIN<br>             CALC=:AFT_DISC_REG *(:DISC/100);<br>             TOTAL_FREE_PURCH=:AFT_DISC_REG;<br> <br>             IF (TOTAL_FREE_USED=0) THEN<br>               BEGIN<br>                 TOTAL_FREE_USED=:TOTAL_DISC;<br>               END<br> <br>            END<br> <br>         ELSE<br>            BEGIN<br> <br>            CALC=0;<br>            TOTAL_FREE_PURCH=0;<br> <br> <br>            END<br>    FREE_DOL_CALC=: FREE_DOL_CALC+ :CALC;<br>    TOTAL=:TOTAL +:TOTAL_REG;<br>    AFT_DISC=:AFT_DISC+:AFT_DISC_REG;<br>    PAID=:PAID + :PAID_REG;<br>    FREE_PURCH_CALC=:FREE_PURCH_CALC+:TOTAL_FREE_PURCH;<br>    FREE_DOL_USED=:FREE_DOL_USED + :TOTAL_FREE_USED;<br>    FREE_DOL_OK=:FREE_DOL_CALC-:FREE_DOL_USED;<br>    END<br> <br> <br>  UPDATE CUST SET FREE_DOL=(:FREE_DOL_CALC- :FREE_DOL_USED),FREE_PURCH=(:FREE_DOL_CALC- :FREE_DOL_USED)*10 WHERE CUST_NUM=:CUST_NUM AND STORE=:CUST_STORE;<br> <br> END<br> <br> <br> end<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_12"></a>
<div class="caption1">UPDATE_BUY_CLUB_CUST</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;UPDATE_BUY_CLUB_CUST&quot; (&quot;CUST_NUM1&quot; Integer) RETURNS (&quot;CUST_NUM&quot; Integer,&quot;CUST_STORE&quot; Integer,&quot;FREE_DOL&quot; Decimal(15,2),&quot;FREE_PURCH&quot; Decimal(15,2),&quot;PAID&quot; Decimal(15,2),&quot;AFT_DISC&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;FREE_DOL_CALC&quot; Decimal(15,2),&quot;FREE_PURCH_CALC&quot; Decimal(15,2),&quot;FREE_DOL_OK&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2))<br> AS<br> <br> DECLARE VARIABLE DISC DECIMAL(15,2);<br> DECLARE VARIABLE DTE1 DATE ;<br> DECLARE VARIABLE DTE2 DATE;<br> DECLARE VARIABLE STORE INTEGER;<br> DECLARE VARIABLE DTE  DATE;<br> DECLARE VARIABLE PERC DECIMAL(15,2);<br> DECLARE VARIABLE CALC DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_REG DECIMAL(15,2);<br> DECLARE VARIABLE AFT_DISC_REG DECIMAL(15,2);<br> DECLARE VARIABLE PAID_REG DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_USED DECIMAL(15,2);<br> DECLARE VARIABLE TOTAL_FREE_PURCH DECIMAL(15,2);<br> <br> BEGIN<br> <br> FOR SELECT CUST_NUM,STORE, FREE_DOL,FREE_PURCH from CUST<br>  where buying_club='T' AND CUST_NUM=:CUST_NUM1<br>  INTO :CUST_NUM,:CUST_STORE,:FREE_DOL,:FREE_PURCH<br>  do<br> <br>  BEGIN<br>   FREE_DOL_CALC=0;<br>   TOTAL=0;<br>   AFT_DISC=0;<br>   PAID=0;<br>   TOTAL_FREE_PURCH=0;<br>   FREE_PURCH_CALC=0;<br>   TOTAL_FREE_USED=0;<br>   FREE_DOL_USED=0;<br>   FOR SELECT TOTAL,PAID,AFT_DISC,FREE_DOL_USED,DTE,STORE,TOTAL_DISC FROM RECEIPT WHERE CUST_NUM=:CUST_NUM AND CUST_STORE=:CUST_STORE<br>   INTO :TOTAL_REG,:PAID_REG,:AFT_DISC_REG,:TOTAL_FREE_USED,:DTE,:STORE,:TOTAL_DISC<br> <br>   DO<br> <br>   BEGIN<br> <br>         SELECT DISC FROM  DISC_BUYIN_CLUB WHERE STORE=:STORE AND DTE1&lt; :DTE AND DTE2&gt; :DTE<br>         INTO :DISC;<br>         IF (:DISC  IS NOT NULL AND :DISC&gt; 0 ) THEN<br>            BEGIN<br>             CALC=:AFT_DISC_REG *(:DISC/100);<br>             TOTAL_FREE_PURCH=:AFT_DISC_REG;<br>             IF (TOTAL_FREE_USED=0) THEN<br>               BEGIN<br>                 TOTAL_FREE_USED=:TOTAL_DISC;<br>               END<br> <br>            END<br> <br>         ELSE<br>            BEGIN<br> <br>            CALC=0;<br>            TOTAL_FREE_PURCH=0;<br> <br> <br>            END<br>    FREE_DOL_CALC=: FREE_DOL_CALC+ :CALC;<br>    TOTAL=:TOTAL +:TOTAL_REG;<br>    AFT_DISC=:AFT_DISC+:AFT_DISC_REG;<br>    PAID=:PAID + :PAID_REG;<br>    FREE_PURCH_CALC=:FREE_PURCH_CALC+:TOTAL_FREE_PURCH;<br>    FREE_DOL_USED=:FREE_DOL_USED + :TOTAL_FREE_USED;<br>    FREE_DOL_OK=:FREE_DOL_CALC-:FREE_DOL_USED;<br>    END<br> <br> <br>   UPDATE CUST SET FREE_DOL=(:FREE_DOL_CALC- :FREE_DOL_USED),FREE_PURCH=(:TOTAL_FREE_PURCH- :FREE_DOL_USED)  WHERE CUST_NUM=:CUST_NUM AND STORE=:CUST_STORE;<br> <br> <br> END<br> <br> <br> end<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
<a name = "Procedure_13"></a>
<div class="caption1">UPDATE_RECEIPT</div><br><br> 
<table class="tabformat" border="0" cellpadding="2" cellspacing="1" width="100%">
 
  <tr>
    <td class="tabdata" > <pre><code>Create procedure &quot;UPDATE_RECEIPT&quot;  RETURNS (&quot;NUM&quot; Integer,&quot;STORE&quot; Integer,&quot;CUST_NUM&quot; Integer,&quot;SUB_TOTAL&quot; Decimal(15,2),&quot;TOTAL_DISC&quot; Decimal(15,2),&quot;AFT_DISC&quot; Decimal(15,2),&quot;TOTAL&quot; Decimal(15,2),&quot;FREE_DOL_USED&quot; Decimal(15,2),&quot;OFF_FRM&quot; Integer,&quot;DISC_FRM&quot; Integer)<br> AS<br> <br> DECLARE VARIABLE FLAG INTEGER;<br> <br> <br> BEGIN<br> <br> FOR SELECT NUM,STORE,CUST_NUM,SUB_TOTAL,TOTAL_DISC,AFT_DISC,TOTAL,FREE_DOL_USED<br> FROM RECEIPT<br> WHERE FREE_DOL_USED&lt;&gt;0 AND AFT_DISC&lt;&gt;SUB_TOTAL -FREE_DOL_USED<br> INTO :NUM,STORE,CUST_NUM,SUB_TOTAL,TOTAL_DISC,AFT_DISC,TOTAL,FREE_DOL_USED<br> <br> DO<br> BEGIN<br> FLAG=0;<br> FOR SELECT OFF_FRM FROM RECLINE WHERE REC_NUM=:NUM AND STORE=:STORE GROUP BY OFF_FRM INTO :OFF_FRM<br>   DO<br>   BEGIN<br>   IF (:OFF_FRM&lt;&gt;0) THEN<br>    FLAG=1;<br>   END<br> DISC_FRM=:FLAG;<br> <br> <br> IF (:FLAG=0) THEN<br>  BEGIN<br>   UPDATE RECEIPT SET FREE_DOL_USED=0 WHERE NUM=:NUM AND STORE=:STORE;<br>  END<br> ELSE<br>  BEGIN<br>    SUSPEND;<br>  END<br> <br> <br> <br> END<br> <br> <br> END<br> ^
</code></pre>   </td>  </tr>
</table>

 <br><br> 
</body></html>
